Skip to content

Work with PostgreSQL Extensions

The PostgreSQL vendor (EDbVendor.dbvpostgresql) handles standard PostgreSQL syntax plus most of the dialect-specific extensions: PL/pgSQL functions, range types, JSONB operators, LATERAL joins, RETURNING, partial indexes, and more.

Quick example

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
using gudusoft.gsqlparser;

var parser = new TGSqlParser(EDbVendor.dbvpostgresql);
parser.sqltext = """
    WITH upd AS (
      UPDATE employees
         SET sales_count = sales_count + 1
       WHERE id = (SELECT sales_person FROM accounts WHERE name = 'Acme Corp')
       RETURNING *
    )
    INSERT INTO employees_log
    SELECT *, current_timestamp FROM upd;
    """;

if (parser.parse() == 0)
    Console.WriteLine($"Parsed {parser.sqlstatements.size()} statement(s).");

Vendor variants — Greenplum and Redshift

Three vendors share PostgreSQL grammar ancestry:

  • EDbVendor.dbvpostgresql — vanilla PostgreSQL.
  • EDbVendor.dbvgreenplum — adds DISTRIBUTED BY, partitioning extensions, OLAP functions.
  • EDbVendor.dbvredshift — Amazon's PostgreSQL fork with DISTKEY/SORTKEY/SUPER columns and the Redshift-specific reserved-word set.

Pick the vendor that matches the target engine — using dbvpostgresql for Redshift SQL will reject SUPER columns and DISTKEY clauses, and using dbvredshift for vanilla PostgreSQL will reserve a few extra Redshift keywords.

Common PostgreSQL features

Feature Notes
RETURNING clause on INSERT/UPDATE/DELETE Fully supported
WITH (CTE) including WITH RECURSIVE Supported on all DML
LATERAL joins Supported
Array & range operators [], &&, <@, @> etc.
JSON / JSONB operators ->, ->>, #>, #>>, ?, ?|, ?&
GENERATED ALWAYS AS IDENTITY Supported
CREATE FUNCTION ... LANGUAGE plpgsql Body is preserved as text — no PL/pgSQL AST
COPY ... FROM STDIN Recognised; payload preserved as text
Window functions with frame clauses RANGE, ROWS, GROUPS

PL/pgSQL function bodies

Unlike Oracle PL/SQL, PostgreSQL's procedural language (PL/pgSQL) is not parsed into an AST. The body inside $$...$$ or $func$...$func$ is preserved verbatim and exposed as a string. If you need to analyse PL/pgSQL, run a separate pass on the body text.

1
2
3
4
5
6
7
8
9
parser.sqltext = """
    CREATE FUNCTION add_one(x INT) RETURNS INT AS $$
    BEGIN
        RETURN x + 1;
    END;
    $$ LANGUAGE plpgsql;
    """;
parser.parse();
// The body 'BEGIN RETURN x + 1; END;' is available as a literal on the function node.

Common pitfalls

  • PL/pgSQL is opaque (see above) — choose Oracle if you need a procedural-language AST.
  • Identifier folding: PostgreSQL folds unquoted identifiers to lowercase. Quoted identifiers preserve case.
  • DISTRIBUTED BY is Greenplum-only — it does not parse under dbvpostgresql.
  • SUPER columns are Redshift-only — they do not parse under dbvpostgresql or dbvgreenplum.