Edit

#PG Query Extension

• ⬅️️ Back

• Features
• Requirements
• Installation
  • Using PIE (Recommended)
  • Supported PostgreSQL Versions
• Loading the Extension
  • In php.ini
  • During Development
• Usage
• Functions Reference
  • pg_query_deparse_opts Parameters
• Error Handling
• Development
  • Build Commands
  • Modifying the Extension
• Architecture
• See Also

A compiled PHP extension for PostgreSQL query parsing using libpg_query.

This extension provides low-level functions for parsing PostgreSQL SQL queries. For a higher-level, object-oriented interface with strongly-typed AST nodes, see the pg-query library.

#Features

• Parse PostgreSQL SQL queries into JSON or protobuf AST
• Generate query fingerprints for query grouping
• Normalize SQL queries (replace literals with placeholders)
• Deparse AST back to SQL with optional pretty-printing
• Parse PL/pgSQL functions
• Split multiple SQL statements
• Scan SQL into tokens
• Generate query summaries for logging/monitoring

#Requirements

• PHP 8.2+
• C compiler (gcc/clang)
• git (for auto-downloading libpg_query)
• make
• protobuf-c library

#Installation

#Using PIE (Recommended)

PIE is the modern PHP extension installer.

# Simple installation (auto-downloads libpg_query for PostgreSQL 17)
pie install flow-php/pg-query-ext

# Install with a specific PostgreSQL grammar version (15, 16, or 17)
pie install flow-php/pg-query-ext --with-pg-version=16

The extension will automatically download and build the appropriate libpg_query version. Build dependencies (protobuf-c, git, make, gcc) must be available on your system.

#Supported PostgreSQL Versions

#Loading the Extension

#In php.ini

extension=pg_query

#During Development

php -d extension=/path/to/pg_query.so your_script.php

#Usage

<?php

// Parse SQL and return JSON AST
$json = pg_query_parse('SELECT * FROM users WHERE id = 1');
$ast = json_decode($json, true);

// Parse SQL and return protobuf AST (more efficient for programmatic use)
$protobuf = pg_query_parse_protobuf('SELECT * FROM users WHERE id = 1');

// Generate fingerprint (same for structurally equivalent queries)
$fp = pg_query_fingerprint('SELECT * FROM users WHERE id = 1');
// Returns same fingerprint for: SELECT * FROM users WHERE id = 2

// Normalize query (replace literals with $N placeholders)
$normalized = pg_query_normalize("SELECT * FROM users WHERE name = 'John'");
// Returns: SELECT * FROM users WHERE name = $1

// Normalize utility/DDL statements
$normalized = pg_query_normalize_utility('CREATE TABLE users (id INT, name VARCHAR(255))');

// Split multiple statements
$statements = pg_query_split('SELECT 1; SELECT 2; SELECT 3');
// Returns: ['SELECT 1', ' SELECT 2', ' SELECT 3']

// Parse PL/pgSQL function
$plpgsql = pg_query_parse_plpgsql('
    CREATE FUNCTION add(a int, b int) RETURNS int AS $$
    BEGIN
        RETURN a + b;
    END;
    $$ LANGUAGE plpgsql;
');

// Scan SQL into tokens (returns protobuf data)
$tokens = pg_query_scan('SELECT 1');

// Deparse protobuf AST back to SQL
$protobuf = pg_query_parse_protobuf('SELECT id, name FROM users WHERE active = true');
$sql = pg_query_deparse($protobuf);
// Returns: SELECT id, name FROM users WHERE active = true

// Deparse with pretty-printing options
$sql = pg_query_deparse_opts(
    $protobuf,
    true,   // pretty_print
    4,      // indent_size
    80,     // max_line_length
    false,  // trailing_newline
    false   // commas_start_of_line
);
// Returns:
// SELECT id, name
// FROM users
// WHERE active = true

// Generate query summary (protobuf format, useful for logging)
$summary = pg_query_summary('SELECT * FROM users WHERE id = 1');

#Functions Reference

#pg_query_deparse_opts Parameters

pg_query_deparse_opts(
    string $protobuf,           // Protobuf AST from pg_query_parse_protobuf()
    bool $pretty_print = false, // Enable pretty printing
    int $indent_size = 4,       // Spaces per indentation level
    int $max_line_length = 80,  // Maximum line length before wrapping
    bool $trailing_newline = false,    // Add trailing newline
    bool $commas_start_of_line = false // Place commas at line start
): string

#Error Handling

The extension throws RuntimeException on parse errors:

<?php

try {
    $result = pg_query_parse('INVALID SQL SYNTAX');
} catch (RuntimeException $e) {
    echo "Parse error: " . $e->getMessage();
}

#Development

#Build Commands

# Build and run tests
make test

# Build only
make build

# Rebuild extension only (without rebuilding libpg_query)
make rebuild

# Clean build artifacts
make clean

# Remove everything including libpg_query
make distclean

#Modifying the Extension

When modifying the C source files:

# Inside nix-shell with --arg with-pg-query-ext true
cd src/extension/pg-query-ext
make rebuild

# Test your changes
make test

#Architecture

The extension is built on top of libpg_query, which extracts PostgreSQL's query parser into a standalone library. This means you get the exact same SQL parsing behavior as PostgreSQL itself.

Key implementation details:

• Static linking: libpg_query.a is statically linked into the extension
• Build dependency: Requires protobuf-c library for compilation (libpg_query uses protobuf internally)
• Auto-download: The build system automatically downloads the correct libpg_query version

#See Also

• pg-query library - Higher-level PHP wrapper with strongly-typed AST nodes
• libpg_query - The underlying C library
• Nix Development Environment - Using nix-shell for development