Skip to content

How to Parse PostgreSQL SQL in Java

To parse PostgreSQL in Java, create a TGSqlParser with EDbVendor.dbvpostgresql and call parse() — GSP understands PostgreSQL-specific syntax natively, including dollar-quoted ($$ ... $$) PL/pgSQL function bodies, RETURNING clauses on DML, WITH / recursive CTEs, and COPY. This guide shows how each construct maps to the AST and which accessors extract the information you need.

Prerequisites

Dollar-Quoted PL/pgSQL Functions

CREATE FUNCTION ... AS $$ ... $$ LANGUAGE plpgsql parses to TCreateFunctionStmt (package gudusoft.gsqlparser.stmt, statement type sstcreatefunction). GSP does not treat the dollar-quoted body as an opaque string — it parses the PL/pgSQL inside in the same pass, so the function's declarations and body statements are directly available:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
import gudusoft.gsqlparser.TGSqlParser;
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.TCustomSqlStatement;
import gudusoft.gsqlparser.stmt.TCreateFunctionStmt;

public class ParsePlpgsqlFunction {
    public static void main(String[] args) {
        TGSqlParser parser = new TGSqlParser(EDbVendor.dbvpostgresql);
        parser.sqltext =
            "CREATE OR REPLACE FUNCTION close_stale_orders(cutoff DATE)\n" +
            "RETURNS integer AS $$\n" +
            "DECLARE\n" +
            "  n integer;\n" +
            "BEGIN\n" +
            "  UPDATE orders SET status = 'closed' WHERE created_at < cutoff;\n" +
            "  INSERT INTO audit_log (msg) VALUES ('closed stale orders');\n" +
            "  RETURN 1;\n" +
            "END;\n" +
            "$$ LANGUAGE plpgsql;";

        if (parser.parse() != 0) {
            System.err.println(parser.getErrormessage());
            return;
        }

        TCreateFunctionStmt func =
            (TCreateFunctionStmt) parser.getSqlstatements().get(0);

        System.out.println("Function: " + func.getFunctionName());
        System.out.println("Returns:  " + func.getReturnDataType());
        System.out.println("Declares: " + func.getDeclareStatements().size());
        System.out.println("Body:     " + func.getBodyStatements().size() + " statements");

        for (TCustomSqlStatement stmt : func.getBodyStatements()) {
            System.out.println("  " + stmt.sqlstatementtype);
        }
    }
}

Key accessors on TCreateFunctionStmt:

  • getFunctionName() / getReturnDataType() — signature information
  • getDeclareStatements() — the DECLARE section (variable and cursor declarations)
  • getBodyStatements() — the BEGIN ... END executable statements
  • getProcedureLanguage() — the LANGUAGE clause

To pull every embedded SELECT/INSERT/UPDATE/DELETE out of function bodies — however deeply nested in IF/LOOP blocks — use the recursive getStatements() walk shown in Parse Oracle PL/SQL; it is vendor-independent.

RETURNING Clauses

PostgreSQL's RETURNING turns DML into a row source. GSP exposes it via getReturningClause(), available on TInsertSqlStatement, TUpdateSqlStatement, TDeleteSqlStatement, and TMergeSqlStatement:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
import gudusoft.gsqlparser.TGSqlParser;
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.stmt.TInsertSqlStatement;
import gudusoft.gsqlparser.nodes.TReturningClause;
import gudusoft.gsqlparser.nodes.TResultColumnList;

public class ParseReturning {
    public static void main(String[] args) {
        TGSqlParser parser = new TGSqlParser(EDbVendor.dbvpostgresql);
        parser.sqltext =
            "INSERT INTO users (name, email)\n" +
            "VALUES ('Ada', 'ada@example.com')\n" +
            "RETURNING id, created_at;";

        if (parser.parse() != 0) {
            System.err.println(parser.getErrormessage());
            return;
        }

        TInsertSqlStatement insert =
            (TInsertSqlStatement) parser.getSqlstatements().get(0);

        TReturningClause returning = insert.getReturningClause();
        if (returning != null) {
            TResultColumnList cols = returning.getResultExprList();
            for (int i = 0; i < cols.size(); i++) {
                System.out.println("RETURNING: " + cols.getResultColumn(i));
            }
        }
    }
}

RETURNING columns are outputs of the statement — for column-level lineage they behave like a SELECT list on top of the DML.

Common Table Expressions (WITH)

Any statement carrying a WITH clause exposes it through getCteList(), which returns a TCTEList of TCTE nodes. This includes PostgreSQL's writable CTEs (WITH upd AS (UPDATE ... RETURNING *) INSERT ...):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
import gudusoft.gsqlparser.TGSqlParser;
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.stmt.TInsertSqlStatement;
import gudusoft.gsqlparser.nodes.TCTE;
import gudusoft.gsqlparser.nodes.TCTEList;

public class ParseCte {
    public static void main(String[] args) {
        TGSqlParser parser = new TGSqlParser(EDbVendor.dbvpostgresql);
        parser.sqltext =
            "WITH upd AS (\n" +
            "  UPDATE employees SET sales_count = sales_count + 1\n" +
            "  WHERE id = (SELECT sales_person FROM accounts WHERE name = 'Acme')\n" +
            "  RETURNING *\n" +
            ")\n" +
            "INSERT INTO employees_log SELECT *, current_timestamp FROM upd;";

        if (parser.parse() != 0) {
            System.err.println(parser.getErrormessage());
            return;
        }

        TInsertSqlStatement insert =
            (TInsertSqlStatement) parser.getSqlstatements().get(0);

        TCTEList cteList = insert.getCteList();
        for (int i = 0; i < cteList.size(); i++) {
            TCTE cte = cteList.getCTE(i);
            System.out.println("CTE name:  " + cte.getTableName());
            System.out.println("Recursive: " + cte.isRecursive());
            if (cte.getSubquery() != null) {
                System.out.println("Defined by SELECT: " + cte.getSubquery());
            } else if (cte.getUpdateStmt() != null) {
                System.out.println("Defined by UPDATE: " + cte.getUpdateStmt());
            }
        }
    }
}

Useful TCTE accessors:

  • getTableName() — the CTE's name
  • getColumnList() — the optional explicit column list
  • getSubquery() — the defining SELECT (a TSelectSqlStatement)
  • getInsertStmt() / getUpdateStmt() / getDeleteStmt() — for data-modifying CTEs
  • isRecursive() — true under WITH RECURSIVE

When extracting table dependencies, remember that a CTE name is a derived relation: references to upd above should resolve to the CTE definition, not to a physical table.

COPY Statements

COPY — PostgreSQL's bulk import/export — parses to TCopyStmt (package gudusoft.gsqlparser.stmt, statement type sstCopy):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
import gudusoft.gsqlparser.TGSqlParser;
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.stmt.TCopyStmt;

public class ParseCopy {
    public static void main(String[] args) {
        TGSqlParser parser = new TGSqlParser(EDbVendor.dbvpostgresql);
        parser.sqltext =
            "COPY country (code, name) FROM '/data/country.csv' WITH (FORMAT csv);";

        if (parser.parse() != 0) {
            System.err.println(parser.getErrormessage());
            return;
        }

        TCopyStmt copy = (TCopyStmt) parser.getSqlstatements().get(0);
        System.out.println("Table:   " + copy.getTargetTable());   // country
        System.out.println("Columns: " + copy.getColumnList());    // code, name
        System.out.println("File:    " + copy.getFilename());      // '/data/country.csv'
    }
}
  • getTargetTable() / getColumnList() identify the affected table and columns
  • getFilename() returns the file (or program) operand
  • getSubQuery() returns the SELECT for the COPY (SELECT ...) TO ... form, where getTargetTable() is null

For lineage purposes, COPY table FROM file is a data source edge into the table and COPY table TO file is a data sink — both are worth capturing when auditing data movement.

Vendor Notes

  • Casts and operators: PostgreSQL-style ::type casts, array subscripts, and ILIKE parse under dbvpostgresql without preprocessing.
  • Identifier folding: unquoted identifiers fold to lowercase (PostgreSQL behavior); override this with custom identifier rules if your environment differs.
  • Related dialects: Greenplum and Amazon Redshift descend from PostgreSQL, but parse them with their own vendors (dbvgreenplum, dbvredshift) — their syntax has diverged.