Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
Hacking PostgreSQL to Gain SQL Parsing Superpowers
Search
Lukas Fittl
April 22, 2016
Programming
1
570
Hacking PostgreSQL to Gain SQL Parsing Superpowers
Talk at Microsoft Reactor / Open Source Show And Tell
https://github.com/lfittl/pg_query
Lukas Fittl
April 22, 2016
Tweet
Share
More Decks by Lukas Fittl
See All by Lukas Fittl
How to Scale Postgres - Automation, Tuning & Sharding
lfittl
0
640
What's Missing for Postgres Monitoring
lfittl
0
230
A Map For Monitoring PostgreSQL
lfittl
2
380
Monitoring Postgres at Scale
lfittl
1
420
Monitoring PostgreSQL at Scale
lfittl
4
260
Postgres Performance for App Developers
lfittl
2
290
GraphQL ❤ PostgreSQL -- P.S. aka BeatQL
lfittl
1
590
PostgreSQL at a Web Startup
lfittl
3
590
Advanced pg_stat_statements: Filtering, Regression Testing & more
lfittl
4
770
Other Decks in Programming
See All in Programming
Git Sync を超える!OSS で実現する CDK Pull 型デプロイ / Deploying CDK with PipeCD in Pull-style
tkikuc
4
360
商品比較サービス「マイベスト」における パーソナライズレコメンドの第一歩
ucchiii43
0
180
Goで作る、開発・CI環境
sin392
0
270
iOS 26にアップデートすると実機でのHot Reloadができない?
umigishiaoi
0
140
Claude Code + Container Use と Cursor で作る ローカル並列開発環境のススメ / ccc local dev
kaelaela
12
7.1k
AI時代のソフトウェア開発を考える(2025/07版) / Agentic Software Engineering Findy 2025-07 Edition
twada
PRO
99
37k
テストから始めるAgentic Coding 〜Claude Codeと共に行うTDD〜 / Agentic Coding starts with testing
rkaga
16
5.6k
バイブスあるコーディングで ~PHP~ 便利ツールをつくるプラクティス
uzulla
1
130
チームで開発し事業を加速するための"良い"設計の考え方 @ サポーターズCoLab 2025-07-08
agatan
1
470
AWS Summit Japan 2024と2025の比較/はじめてのKiro、今あなたは岐路に立つ
satoshi256kbyte
1
140
スタートアップの急成長を支えるプラットフォームエンジニアリングと組織戦略
sutochin26
1
7.3k
[SRE NEXT] 複雑なシステムにおけるUser Journey SLOの導入
yakenji
0
150
Featured
See All Featured
Helping Users Find Their Own Way: Creating Modern Search Experiences
danielanewman
29
2.7k
Java REST API Framework Comparison - PWX 2021
mraible
31
8.7k
Learning to Love Humans: Emotional Interface Design
aarron
273
40k
Product Roadmaps are Hard
iamctodd
PRO
54
11k
A better future with KSS
kneath
238
17k
The Cult of Friendly URLs
andyhume
79
6.5k
Templates, Plugins, & Blocks: Oh My! Creating the theme that thinks of everything
marktimemedia
31
2.4k
Making the Leap to Tech Lead
cromwellryan
134
9.4k
GraphQLの誤解/rethinking-graphql
sonatard
71
11k
個人開発の失敗を避けるイケてる考え方 / tips for indie hackers
panda_program
108
19k
StorybookのUI Testing Handbookを読んだ
zakiyama
30
5.9k
Statistics for Hackers
jakevdp
799
220k
Transcript
@LukasFittl Hacking PostgreSQL to Gain SQL Parsing Superpowers @LukasFittl
@LukasFittl Parsing SQL
@LukasFittl SELECT * FROM x WHERE z = 1
@LukasFittl http://xkcd.com/208/
@LukasFittl PostgreSQL
@LukasFittl EXPLAIN (PARSETREE TRUE) SELECT * FROM x
WHERE y = 1 Unfortunately doesn’t exist.
@LukasFittl Parse Statement raw_parse(..) pg_catalog Rewrite Query Query Planner Execute
How PostgreSQL runs a Query:
@LukasFittl tree = raw_parser(query_str); str = nodeToString(tree); printf(str); ({SELECT :distinctClause
<> :intoClause <> :targetList ( {RESTARGET :name <> :indirection <> :val {COLUMNREF :fields ({A_STAR}) :location 7} :location 7}) :fromClause ( {RANGEVAR :schemaname <> :relname x :inhOpt 2 :relpersistence p :alias <> :location 14}) :whereClause {AEXPR :name (“=") :lexpr {COLUMNREF :fields ("y") :location 22} :rexpr {PARAMREF :number 0 :location 26} :location 24} :groupClause <> :havingClause <> :windowClause <>
@LukasFittl Parse Statement raw_parse(..) pg_catalog Rewrite Query Query Planner Execute
@LukasFittl PgQuery._raw_parse( “SELECT * FROM x WHERE y = 1”)
({SELECT :distinctClause <> :intoClause <> :targetList ( {RESTARGET :name <> :indirection <> :val {COLUMNREF :fields ({A_STAR}) :location 7} :location 7}) :fromClause ( {RANGEVAR :schemaname <> :relname x :inhOpt 2 :relpersistence p :alias <> :location 14}) :whereClause {AEXPR :name (“=") :lexpr {COLUMNREF :fields ("y") :location 22} :rexpr {PARAMREF :number 0 :location 26} :location 24} :groupClause <> :havingClause <> :windowClause <> :valuesLists <>
@LukasFittl PgQuery._raw_parse( “SELECT * FROM x WHERE y = 1”)
[{ "SelectStmt": { "targetList": [{ "ResTarget": { "val": { "ColumnRef": { "fields": [{ "A_Star": {} }], "location": 7 } }, "location": 7 } }], "fromClause": [{ "RangeVar": { "relname": "x", "inhOpt": 2, "relpersistence": "p", "location": 14 } }], "whereClause": { "A_Expr": {
@LukasFittl github.com/lfittl/pg_query
@LukasFittl De-Parsing SQL
@LukasFittl SELECT * FROM the_table SELECT * FROM a_better_table
@LukasFittl q = PgQuery.parse(‘SELECT * FROM the_table’) =>
[{"SelectStmt" => { "targetList" =>[…], "fromClause" => [{ "RangeVar" => { "relname" => “the_table", … } }] }}] q.tree[0][‘SelectStmt']['fromClause'][0][‘RangeVar'] ['relname'] = ‘the_other_table' q.deparse => "SELECT * FROM \"the_other_table\"
@LukasFittl Don’t like JSON?
@LukasFittl pg_query_go package main import ( "github.com/lfittl/pg_query_go"
) func main() { tree, err := pg_query.Parse("SELECT 1") if err != nil { panic(err); } … }
@LukasFittl pg_query.ParsetreeList{ Statements: []nodes.Node{
nodes.SelectStmt{ TargetList: []nodes.Node{ nodes.ResTarget{ Val: nodes.A_Const{ Type: "integer", Val: nodes.Value{ Type: nodes.T_Integer, Ival: 1, }, Location: 7, }, Location: 7, }, }, }, }, tree := pg_query.Parse(“SELECT 1")
@LukasFittl Other Languages C/C++: libpg_query Go: pg_query_go Node.js: pg-query-parser Not
yet: Python, Java, [?]
@LukasFittl Shameless Plug: github.com/citusdata/citus Open-source, distributed PostgreSQL
@LukasFittl Thank You! github.com/lfittl/pg_query