pgsqlparser 2.0.0

PgSqlParser

.NET version of https://github.com/pganalyze/libpg_query. This .NET wrapper on libpg_query C library which uses the actual PostgreSQL server source to parse SQL queries and return the internal PostgreSQL parse tree.

You can find further background to why a query's parse tree is useful here: https://pganalyze.com/blog/parse-postgresql-queries-in-ruby.html

Installation

dotnet add package pgsqlparser

Note that the libpg_query libs for all OS'es are already packaged with the assembly.

This version is built on libpg_query 18.1.0, which uses the PostgreSQL 18.6 parser. It targets .NET 8, .NET 9 and .NET 10, and is compatible with trimming and Native AOT.

Usage

All functions support both sync and async versions.

Working with results

Every function returns a Result<T> that holds either a Value or an Error. Pick the style that suits your code:

using PgSqlParser;

// Check IsSuccess: the compiler then knows Value (or Error) is not null
var result = Parser.Parse(query);
if (result.IsSuccess)
{
    Console.WriteLine(result.Value.Stmts.Count);
}
else
{
    Console.WriteLine(result.Error.Message);
}

// Throw on failure: PgSqlParserException carries the Error
var tree = Parser.Parse(query).GetValueOrThrow();

// Try pattern
if (Parser.Parse(query).TryGetValue(out var parsed, out var error))
{
    Console.WriteLine(parsed.Stmts.Count);
}

// Match and deconstruction
var text = Parser.Normalize(query).Match(value => value, e => e.Message);
var (normalized, normalizeError) = Parser.Normalize(query);

Normalize

Transform DML query (SELECT, INSERT, UPDATE, DELETE) into a canonical form by replacing literal values (constants) with placeholders ($1, $2)

using PgSqlParser;

var query = "SELECT 1";
var result = Parser.Normalize(query);

if (result.Error is null)
{
    Console.WriteLine(result.Value);
}

// Normalized value: SELECT $1

NormalizeUtility

Transform DDL and other utility commands (CREATE TABLE, ALTER TABLE, VACUUM, and ANALYZE et.al.) into a canonical form by replacing literal values (constants) with placeholders ($1, $2)

using PgSqlParser;

var query = "CREATE ROLE postgres PASSWORD 'xyz'";
var result = Parser.NormalizeUtility(query);

if (result.Error is null)
{
    Console.WriteLine(result.Value);
}

// Normalized Utility value: CREATE ROLE postgres PASSWORD $1

Parse

Parse SQL and return a Protobuf based AST

using PgSqlParser;

var query = "SELECT 1; SELECT 2";
        
var result = Parser.Parse(query);

if (result.Error is null)
{
    Console.WriteLine(result.Value);
}

// result.Value is a ParseResult AST object and the serialized JSON output is as below
// { "version": 180006, "stmts": [ { "stmt": { "SelectStmt": { "targetList": [ { "ResTarget": { "val": { "A_Const": { "ival": { "ival": 1 }, "location": 7 } }, "location": 7 } } ], "limitOption": "LIMIT_OPTION_DEFAULT", "op": "SETOP_NONE" } }, "stmt_len": 8 }, { "stmt": { "SelectStmt": { "targetList": [ { "ResTarget": { "val": { "A_Const": { "ival": { "ival": 2 }, "location": 17 } }, "location": 17 } } ], "limitOption": "LIMIT_OPTION_DEFAULT", "op": "SETOP_NONE" } }, "stmt_location": 10 } ] }

Parse can also take ParserOptions as a list of flags i.e. ParserOptions.DisableBackslashQuote | ParserOptions.DisableEscapeStringWarning

Navigating the parse tree

The parse tree has more than 270 node types, and reaching a node by hand means spelling out the whole path to it. These helpers avoid that:

using PgSqlParser;

var query = "SELECT c.name FROM customers c JOIN orders o ON o.customer_id = c.id; DROP TABLE tmp";
var tree = Parser.Parse(query).GetValueOrThrow();

// Every node of a type, anywhere in the tree
foreach (var table in tree.Descendants<RangeVar>())
{
    Console.WriteLine(table.Relname);
}
// Output: customers, orders (DROP TABLE names its target as a plain name list, not a RangeVar)

// Every node, with its parent and depth
foreach (var visit in tree.Walk())
{
    Console.WriteLine($"{new string(' ', visit.Depth * 2)}{visit.Node.Descriptor.Name}");
}

// Pattern match on a statement without the .SelectStmt / .DropStmt property chain
foreach (var stmt in tree.Stmts)
{
    var kind = stmt.Stmt.Unwrap() switch
    {
        SelectStmt => "select",
        DropStmt => "drop",
        _ => "other"
    };
    // The statement's text and kind
    Console.WriteLine($"{kind}: {stmt.GetText(query)}");
}
// Output:
// select: SELECT c.name FROM customers c JOIN orders o ON o.customer_id = c.id
// drop: DROP TABLE tmp

Walk and Descendants visit parents before their children and siblings in field order, which is not always the order of the query text. GetLocation() returns a node's location if it has one; see Offsets and non-ASCII text for its unit. Only statements record a length, so GetText is available for statements and not for other nodes.

Scan

Tokenize a query. Each token has its kind, its keyword kind and its Start and End offsets in the query string.

using PgSqlParser;

var query = "SELECT 1";
var result = Parser.Scan(query);

if (result.Error is null)
{
    foreach (var token in result.Value!.Tokens)
    {
        Console.WriteLine($"{query[token.Start..token.End]}: {token.Token}");
    }
}

// result.Value is a ScanResult object and the serialized JSON output is as below
// { "version": 180006, "tokens": [ { "end": 6, "token": "SELECT", "keywordKind": "RESERVED_KEYWORD" }, { "start": 7, "end": 8, "token": "ICONST" } ] }

ParsePlpgsql

Parse PL/pgSQL function bodies and return a JSON representation

using PgSqlParser;

var sql = """
CREATE OR REPLACE FUNCTION get_all_foo() RETURNS SETOF foo AS
$BODY$
DECLARE
r foo%rowtype;
BEGIN
FOR r IN
SELECT * FROM foo WHERE fooid > 0
  LOOP
      -- can do some processing here
      RETURN NEXT r; -- return current row of SELECT
END LOOP;
  RETURN;
END
$BODY$
LANGUAGE plpgsql;
""";

var result = Parser.ParsePlpgsql(sql);

if (result.Error is null)
{
    Console.WriteLine(result.Value);
}

// return JSON string
// [{"PLpgSQL_function":{"datums":[{"PLpgSQL_var":{"refname":"found","datatype":{"PLpgSQL_type":{"typname":"bool"}}}},{"PLpgSQL_var":{"refname":"r","lineno":3,"datatype":{"PLpgSQL_type":{"typname":"foo%rowtype"}}}},{"PLpgSQL_row":{"refname":"(unnamed row)","lineno":5,"fields":[{"name":"r","varno":1}]}}],"action":{"PLpgSQL_stmt_block":{"lineno":4,"body":[{"PLpgSQL_stmt_fors":{"lineno":5,"var":{"PLpgSQL_row":{"refname":"(unnamed row)","lineno":5,"fields":[{"name":"r","varno":1}]}},"body":[{"PLpgSQL_stmt_return_next":{"lineno":9}}],"query":{"PLpgSQL_expr":{"query":"SELECT * FROM foo WHERE fooid \u003e 0","parseMode":0}}}},{"PLpgSQL_stmt_return":{"lineno":11}}]}}}}]

ParsePlpgsqlFunctions returns the same information as objects, one PlpgsqlFunction per CREATE FUNCTION or DO statement:

var function = Parser.ParsePlpgsqlFunctions(sql).GetValueOrThrow()[0];

// Variables and parameters
foreach (var datum in function.Datums)
{
    Console.WriteLine($"{datum.Kind}: {datum.GetString("refname")}");
}

// Every statement, including nested ones
foreach (var statement in function.Statements())
{
    Console.WriteLine($"line {statement.LineNo}: {statement.Kind}");
}

// The SQL inside the function, ready to pass to Parser.Parse
foreach (var query in function.Queries())
{
    Console.WriteLine(query);
}
// Output: SELECT * FROM foo WHERE fooid > 0

libpg_query has no schema for its PL/pgSQL output, so a node (PlpgsqlNode) exposes its Kind, its Children and its raw Json (a JsonElement) and not typed properties per statement kind.

Fingerprint

Generate a normalized hash (fingerprint) of a SQL statement, ignoring literals, whitespace, and minor variations

using PgSqlParser;

var query = "SELECT 1";
var result = Parser.Fingerprint(query);

if (result.Error is null)
{
    Console.WriteLine(result.Value);
}

/// output: 50fde20626009aba

Fingerprints follow the PostgreSQL 18 query ID rules by default: in SELECT/DML statements a table alias replaces the relation name, and schema names are ignored. Pass FingerprintOptions to change this, for example to get the same fingerprints as PostgreSQL 17 and earlier:

var result = Parser.Fingerprint(query, ParserOptions.Default, FingerprintOptions.RangeVarPg17Compat);

IsUtilityStmt

Check whether each statement in a query is a utility statement (DDL and other commands that are not SELECT, INSERT, UPDATE, DELETE or MERGE)

using PgSqlParser;

var query = "SELECT 1; SET fsync = off";
var result = Parser.IsUtilityStmt(query);

if (result.Error is null)
{
    Console.WriteLine(string.Join(", ", result.Value!));
}

// Output: False, True

Summary

Summarize a query: the tables, aliases, CTE names, functions, filter columns and statement types it references. Pass a truncateLimit to also get a shortened version of the query text.

using PgSqlParser;

var query = "SELECT lower(x.name) FROM public.test AS x WHERE x.a = 1";
var result = Parser.Summary(query);

if (result.Error is null)
{
    Console.WriteLine(result.Value);
}

// Return a `SummaryResult` and the respective JSON string is as below:
// { "tables": [ { "name": "public.test", "schemaName": "public", "tableName": "test", "context": "Select" } ], "aliases": { "x": "public.test" }, "functions": [ { "name": "lower", "functionName": "lower", "context": "Call" } ], "filterColumns": [ { "tableName": "x", "column": "a" } ], "statementTypes": [ "SelectStmt" ] }

SplitWithScanner

Split a SQL script containing multiple statements into an array of clean, standalone SQL statements using lexical (token-based) analysis.

using PgSqlParser;

var query = "SELECT 1; SELECT 2";
var result = Parser.SplitWithScanner(query);

if (result.Error is null)
{
    Console.WriteLine(JsonSerializer.Serialize(result.Value));
}

// Return a `SplitResult` and the respective serialized JSON string is as below:
// {"Statements":[{"Location":0,"Length":8,"Text":"SELECT 1"},{"Location":9,"Length":9,"Text":" SELECT 2"}]}

SplitWithParser

Split a SQL script containing multiple statements into an array of clean, standalone SQL statements using Postgres full parser

using PgSqlParser;

var query = "SELECT 1; SELECT 2";
var result = Parser.SplitWithParser(query);

if (result.Error is null)
{
    Console.WriteLine(JsonSerializer.Serialize(result.Value));
}

// Return a `SplitResult` and the respective serialized JSON string is as below:
// {"Statements":[{"Location":0,"Length":8,"Text":"SELECT 1"},{"Location":10,"Length":8,"Text":"SELECT 2"}]}

Deparse

Deparse AST back into a query string

using PgSqlParser;

var parseJson = """
{ "version": 180006, "stmts": [ { "stmt": { "SelectStmt": { "targetList": [ { "ResTarget": { "val": { "A_Const": { "ival": { "ival": 1 }, "location": 7 } }, "location": 7 } } ], "limitOption": "LIMIT_OPTION_DEFAULT", "op": "SETOP_NONE" } } } ] }
""";

var parseResult = ParseResult.Parser.ParseJson(parseJson); ;
var result = Parser.Deparse(parseResult);

if (result.Error is null)
{
    Console.WriteLine(result.Value);
}

// Output: SELECT 1

Pass DeparseOptions to pretty print the output:

var parseResult = Parser.Parse("SELECT a, b FROM t WHERE x = 1 AND y = 2").Value!;
var result = Parser.Deparse(parseResult, new DeparseOptions { PrettyPrint = true });

// Output:
// SELECT a, b
// FROM t
// WHERE
//     x = 1
//     AND y = 2

Comments are not part of the parse tree. To keep them, extract them with DeparseComments and pass them back in:

var query = "SELECT 1 /* one */";
var parseResult = Parser.Parse(query).Value!;
var comments = Parser.DeparseComments(query).Value!;
var result = Parser.Deparse(parseResult, new DeparseOptions { Comments = comments });

// Output: SELECT 1 /* one */

Offsets and non-ASCII text

.NET strings are UTF-16 while libpg_query works on UTF-8 bytes, so the unit of an offset depends on where it comes from:

• Scan token Start/End and SplitStmt.Location/Length are UTF-16 offsets, ready to use with string.Substring.
• Parse tree locations (for example RawStmt.StmtLocation and node Location) and DeparseComment.MatchLocation are UTF-8 byte offsets. Use Utf8OffsetMapper to convert them:

using PgSqlParser.Utils;

var mapper = new Utf8OffsetMapper(query);
var charOffset = mapper.ToCharOffset(node.Location);

It also converts the other way, for example to compare a position in the string (such as an editor caret) against parse tree locations:

var byteOffset = mapper.ToByteOffset(caretIndex);

• Error.CursorPos is a 1-based position in Unicode code points, as PostgreSQL reports it. Use Error.GetCursorCharOffset(query) to get the matching string offset:

var result = Parser.Parse(query);
if (result.Error is { } error)
{
    var charOffset = error.GetCursorCharOffset(query);
    if (charOffset >= 0)
    {
        Console.WriteLine($"{error.Message}: {query[charOffset..]}");
    }
}

Parse Errors

If the SQL has some error while parsing then the result contains an Error object which provides details of the error.

using PgSqlParser;

var query = "SELEC 1";
var result = Parser.Parse(query);
if (result.Error is not null)
{
    Console.WriteLine(result.Error.Message);
} 

// Output: syntax error at or near "SELEC"

The Error also carries CursorPos, the position of the error in the query (see Offsets and non-ASCII text), and the PostgreSQL source location that raised it in FuncName, FileName and LineNo.

Two kinds of invalid input are caught before the query reaches libpg_query:

• A null query throws ArgumentNullException.
• A query containing a NUL character (\0) returns an Error with the message query contains a NUL character. PostgreSQL does not allow NUL in queries.

Deeply nested queries, such as a long chain of operators without parentheses (a || b || c ...) or many nested subqueries, are read on a dedicated thread with a large enough stack, so they cannot overflow the stack of the calling thread. A parse tree nested more than 4000 levels deep is rejected with an Error. The PostgreSQL parser has its own limit, which depends on the stack available to the calling thread and reports stack depth limit exceeded.

Protobuf's own recursive operations on a parse tree, such as ToString(), Clone() and Equals(), run on your thread. On a very deeply nested tree they can still overflow a small stack.

What's new in 2.0

• PostgreSQL 18: built on libpg_query 18.1.0, which uses the PostgreSQL 18.6 parser and includes its security fix for pg_query_normalize.
• More platforms: Linux on ARM64, Alpine and other musl-based Linux, and Windows on ARM64. The Linux and macOS libraries are now built against old OS baselines, so they load on older systems too (see the table under Installation).
• .NET 10, alongside .NET 8 and .NET 9, and compatibility with trimming and Native AOT.
• New functions: IsUtilityStmt, Summary, DeparseComments and ParsePlpgsqlFunctions.
• Deparse options: pretty printing and keeping comments, through DeparseOptions.
• Fingerprint options: FingerprintOptions, including a PostgreSQL 17 compatible mode.
• Easier results: GetValueOrThrow, TryGetValue, Match and deconstruction on Result<T> (see Working with results).
• Parse tree navigation: Descendants<T>, Walk, Unwrap, GetLocation and GetText (see Navigating the parse tree).
• Correct offsets for non-ASCII text: Scan and split results index the query string directly, with Utf8OffsetMapper and Error.GetCursorCharOffset for the rest (see Offsets and non-ASCII text).
• Safer input handling: a null query, a NUL character or a deeply nested query no longer crashes the process or is silently cut short.
• Package: IntelliSense documentation, a symbol package and Source Link are included.

Upgrading from 1.x

Version 2.0 moves from the PostgreSQL 17 parser to PostgreSQL 18, which changes some results:

• Parse tree: node classes and fields follow PostgreSQL 18, and ParseResult.Version is 180006.
• Fingerprints: the default now follows PostgreSQL 18 query ID rules, so stored fingerprints for queries that use table aliases or schema names will change. Pass FingerprintOptions.RangeVarPg17Compat to keep the previous relation handling.
• Statement locations: a statement now starts at its first non-whitespace, non-comment character. This affects SplitWithParser and RawStmt.StmtLocation; for SELECT 1; SELECT 2 the second statement is at location 10 with length 8, where it was 9 and 9.
• Scan and Split offsets: Scan token offsets and SplitStmt locations are UTF-16 offsets into the query string. In 1.x they were UTF-8 byte offsets, which differ as soon as the query contains non-ASCII text. Remove any conversion you did yourself.
• Errors: Error.FuncName and Error.FileName now hold the function and file name. In 1.x FuncName held the message and the file name was missing.
• Parse return type: Parse and ParseAsync return Result<ParseResult>, where 1.x returned Result<ParseResult?>. Code that declared the nullable type explicitly needs updating.
• Dependencies: the minimum Google.Protobuf version is 3.36.2.

License

PgSqlParser is licensed under the MIT License.

Copyright (c) 2026, Babu Annamalai babu.annamalai@gmail.com

Refer to libpg_query license for license details on libpg_query.

This project includes code derived from the PostgreSQL project, see LICENSE.POSTGRESQL for details.