Builds on the excellent work of libpq_query to use the postgres (v12) source code to parse the SQL.
This package builds an AST designed to make walking and mutating easier. Several basic helper functions are also included.
In addition, it was built with a focus on ease of adding features and not performance. The is no intention to optimize for zero copy or minimum allocations. Do NOT use pgtree in a performance critical process.
pgtree is used by foji to:
- Validate and format the SQL
- Convert developer friendly params into valid SQL parameter references (example)
- Convert wildcard return attributes to point in time data model attributes (
*=> list of fields )
go get github.com/gofoji/pgtreeThis has a dependency on the postgres source code (compiled with C) and can impact build times significantly. As of right now this also impacts installation on Windows.
sql := "select * from foo left join bar on foo.id = bar.id;"
root, err := pgtree.Parse(sql)
if err != nil {
return err
}The return value is a Node which is used in all the following examples.
outSQL, err := pgtree.Print(root)
if err != nil {
return err
}
println(outSQL)Output
SELECT * FROM foo LEFT JOIN bar ON foo.id = bar.id;outSQL, err := pgtree.PrettyPrint(root)
if err != nil {
return err
}
println(outSQL)Output
SELECT
*
FROM
foo
LEFT JOIN bar ON foo.id = bar.id;Finds all reference tables in the statement, including sub queries and joins
sql := "SELECT * FROM foo LEFT JOIN bar ON foo.id = bar.id"
root, _ := pgtree.Parse(sql)
tables := pgtree.ExtractTables(root)
fmt.Println(tables)Output
[`foo` `bar`]
sql := "select * from foo where id = @myParam"
root, _ := pgtree.Parse(sql)
params := pgtree.ExtractParams(root)
fmt.Println(params)Output
[`myparam = id`]
The returned QueryParam object includes a reference to the referenced column (id in this example) if it is available. This is useful for type inference.
Building on the above example you can automatically replace all the instances of the named parameters with the place holder syntax $#
pgtree.ReplaceParams(&root, params)
outSQL, _ := pgtree.Print(root)
fmt.Println(outSQL)Output
SELECT * FROM foo WHERE id = $1;Visiting the SQL AST requires a function to match the Visitor signature:
type Visitor func(node Node, stack []Node, v Visitor) VisitorThe first parameter is the current Node being visited, the stack provides the stack up to the root, the last parameter is the Visitor func.
The returned value of the Walk is the visitor, passing back the input continues the traversal as usual, returning nil will stop walking the current branch at that node. This is used in the example below, as there is no reason to continue walking after finding a RangeVar. You can also return a different visitor func to change the downstream processing in more complex scenarios. See the next section on Debugging for additional help when writing visitors.
func ExtractTables(node nodes.Node) []TableRef {
var result []TableRef
Walk(node, nil, func(node nodes.Node, stack []nodes.Node, v Visitor) Visitor {
switch n := node.(type) {
case *nodes.RangeVar:
t := TableRef{
Catalog: n.Catalogname,
Schema: n.Schemaname,
Table: n.Relname,
Ref: n,
}
if n.Alias != nil {
t.Alias = n.Alias.Aliasname
}
result = append(result, t)
return nil
}
return v
})
return result
}When writing custom visitors and mutations it can be helpful to print the walk tree.
sql := "SELECT * FROM foo LEFT JOIN bar ON foo.id = bar.id"
root, _ := pgtree.Parse(sql)
_, _ = pgtree.Debug(root)Output
Root = `SELECT * FROM foo LEFT JOIN bar ON foo.id = bar.id; \n `
Nodes = `SELECT * FROM foo LEFT JOIN bar ON foo.id = bar.id; \n `
RawStmt = `SELECT * FROM foo LEFT JOIN bar ON foo.id = bar.id; \n `
SelectStmt = `SELECT * FROM foo LEFT JOIN bar ON foo.id = bar.id`
JoinExpr = `foo LEFT JOIN bar ON foo.id = bar.id`
AExpr = `foo.id = bar.id`
String = `=`
ColumnRef = `bar.id`
String = `id`
String = `bar`
ColumnRef = `foo.id`
String = `id`
String = `foo`
RangeVar = `bar`
RangeVar = `foo`
ResTarget = `*`
ColumnRef = `*`
AStar = `*`
JoinExpr = `foo \n LEFT JOIN bar ON foo.id = bar.id`
AExpr = `foo.id = bar.id`
String = `=`
ColumnRef = `bar.id`
String = `id`
String = `bar`
ColumnRef = `foo.id`
String = `id`
String = `foo`
RangeVar = `bar`
RangeVar = `foo`
ResTarget = `*`
ColumnRef = `*`
AStar = `*`
To view the json view of the SQL ast
sql := "SELECT * FROM foo LEFT JOIN bar ON foo.id = bar.id"
s, _ := pgtree.JSON(sql)
println(s)Output (pretty printed for readability)
[
{
"RawStmt": {
"stmt": {
"SelectStmt": {
"targetList": [
{
"ResTarget": {
"val": {
"ColumnRef": {
"fields": [
{
"A_Star": {}
}
],
"location": 7
}
},
"location": 7
}
}
],
"fromClause": [
{
"JoinExpr": {
"jointype": "JOIN_LEFT",
"larg": {
"RangeVar": {
"relname": "foo",
"inh": true,
"relpersistence": "p",
"location": 14
}
},
"rarg": {
"RangeVar": {
"relname": "bar",
"inh": true,
"relpersistence": "p",
"location": 28
}
},
"quals": {
"A_Expr": {
"name": [
{
"String": {
"str": "="
}
}
],
"lexpr": {
"ColumnRef": {
"fields": [
{
"String": {
"str": "foo"
}
},
{
"String": {
"str": "id"
}
}
],
"location": 35
}
},
"rexpr": {
"ColumnRef": {
"fields": [
{
"String": {
"str": "bar"
}
},
{
"String": {
"str": "id"
}
}
],
"location": 44
}
},
"location": 42
}
}
}
}
]
}
}
}
}
]Currently this uses the V12 branch of libpg_query which seems to fail for most cases when using the ProtoBuf serializer, instead we use the JSON format as the output of the Postgres core library.
parse_tree.proto pulled from libpg_query PR67
- Define additional formatting options
- Add verbose option to inject long names
- Add concise option to convert syntax shorthands. Example:
CREATE TABLE table_name AS
TABLE table1;
-- Parser expands to:
CREATE TABLE table_name AS
SELECT * FROM table1;- Godocs
- Move generated code to internal package