pg_dump Reference
Reference pg_dump's implementation for correct system catalog queries and schema extraction patterns.
Source
Repository: https://github.com/postgres/postgres/blob/master/src/bin/pg_dump/
Key files:
pg_dump.c - Main implementation with all system catalog queriespg_dump.h - Data structurespg_dump_sort.c - Dependency sortingcommon.c - Shared catalog query utilities
Object Type → pg_dump Function → System Catalogs
| Object |
Function |
Catalogs |
| Tables & Columns |
getTables() |
pg_class, pg_attribute, pg_type |
| Constraints |
getConstraints() |
pg_constraint |
| Indexes |
getIndexes() |
pg_index, pg_class |
| Triggers |
getTriggers() |
pg_trigger, pg_proc |
| Functions |
getFuncs() |
pg_proc |
| Views |
getViews() |
pg_class, pg_rewrite |
| Sequences |
getSequences() |
pg_sequence, pg_class |
| Policies |
getPolicies() |
pg_policy |
| Types |
getTypes() |
pg_type |
| Aggregates |
getAggregates() |
pg_aggregate, pg_proc |
| Comments |
getComments() |
pg_description |
Key Helper Functions
pg_get_expr(expr, relation, pretty) - Deparse expressions (defaults, WHEN clauses, index predicates)pg_get_constraintdef(oid, pretty) - Get constraint DDLpg_get_indexdef(oid, column, pretty) - Get index DDLpg_get_triggerdef(oid, pretty) - Get full trigger DDL
Important: For trigger WHEN clauses, always use pg_get_expr(t.tgqual, t.tgrelid, false) from pg_catalog.pg_trigger. Do NOT use information_schema.triggers.action_condition.
Workflow
- Identify the object type you're implementing
- Find the pg_dump function from the table above
- Read the system catalog query — note which columns, joins, and helper functions are used
- Check version-specific handling — pg_dump uses
fout->remoteVersion checks
- Adapt for pgschema in
ir/inspector.go or ir/queries/:
- Use pgx parameter binding
- Handle NULLs appropriately
- Add version detection if needed
When pg_dump is Authoritative
Always reference for: system catalog query patterns, correct use of pg_get_* functions, version-specific feature detection, object dependency tracking.
When NOT to Copy pg_dump
Don't copy: output formatting (pgschema has different conventions), archive/restore logic, full-database scope (pgschema is schema-focused), pre-PG14 compatibility.
pgschema Adaptation Pattern
// pg_dump query pattern:
// SELECT t.tgname, pg_get_expr(t.tgqual, t.tgrelid, false) as when_clause
// FROM pg_catalog.pg_trigger t WHERE ...
// pgschema adaptation (in ir/inspector.go or ir/queries/):
query := `SELECT t.tgname, pg_get_expr(t.tgqual, t.tgrelid, false)
FROM pg_catalog.pg_trigger t
WHERE t.tgrelid = $1 AND NOT t.tgisinternal`
rows, err := conn.Query(ctx, query, tableOID)
1---2name: pg-dump3description: Consult PostgreSQL's pg_dump implementation for guidance on system catalog queries and schema extraction when implementing pgschema features. Use this skill when adding new schema object support, debugging inspector.go queries, understanding how PostgreSQL represents objects internally, or handling version-specific features across PostgreSQL 14-18.4---56# pg_dump Reference78Reference pg_dump's implementation for correct system catalog queries and schema extraction patterns.910## Source1112**Repository**: https://github.com/postgres/postgres/blob/master/src/bin/pg_dump/1314**Key files**:15- `pg_dump.c` - Main implementation with all system catalog queries16- `pg_dump.h` - Data structures17- `pg_dump_sort.c` - Dependency sorting18- `common.c` - Shared catalog query utilities1920## Object Type → pg_dump Function → System Catalogs2122| Object | Function | Catalogs |23|--------|----------|----------|24| Tables & Columns | `getTables()` | `pg_class`, `pg_attribute`, `pg_type` |25| Constraints | `getConstraints()` | `pg_constraint` |26| Indexes | `getIndexes()` | `pg_index`, `pg_class` |27| Triggers | `getTriggers()` | `pg_trigger`, `pg_proc` |28| Functions | `getFuncs()` | `pg_proc` |29| Views | `getViews()` | `pg_class`, `pg_rewrite` |30| Sequences | `getSequences()` | `pg_sequence`, `pg_class` |31| Policies | `getPolicies()` | `pg_policy` |32| Types | `getTypes()` | `pg_type` |33| Aggregates | `getAggregates()` | `pg_aggregate`, `pg_proc` |34| Comments | `getComments()` | `pg_description` |3536## Key Helper Functions3738- `pg_get_expr(expr, relation, pretty)` - Deparse expressions (defaults, WHEN clauses, index predicates)39- `pg_get_constraintdef(oid, pretty)` - Get constraint DDL40- `pg_get_indexdef(oid, column, pretty)` - Get index DDL41- `pg_get_triggerdef(oid, pretty)` - Get full trigger DDL4243**Important**: For trigger WHEN clauses, always use `pg_get_expr(t.tgqual, t.tgrelid, false)` from `pg_catalog.pg_trigger`. Do NOT use `information_schema.triggers.action_condition`.4445## Workflow46471. **Identify the object type** you're implementing482. **Find the pg_dump function** from the table above493. **Read the system catalog query** — note which columns, joins, and helper functions are used504. **Check version-specific handling** — pg_dump uses `fout->remoteVersion` checks515. **Adapt for pgschema** in `ir/inspector.go` or `ir/queries/`:52 - Use pgx parameter binding53 - Handle NULLs appropriately54 - Add version detection if needed5556## When pg_dump is Authoritative5758Always reference for: system catalog query patterns, correct use of `pg_get_*` functions, version-specific feature detection, object dependency tracking.5960## When NOT to Copy pg_dump6162Don't copy: output formatting (pgschema has different conventions), archive/restore logic, full-database scope (pgschema is schema-focused), pre-PG14 compatibility.6364## pgschema Adaptation Pattern6566```go67// pg_dump query pattern:68// SELECT t.tgname, pg_get_expr(t.tgqual, t.tgrelid, false) as when_clause69// FROM pg_catalog.pg_trigger t WHERE ...7071// pgschema adaptation (in ir/inspector.go or ir/queries/):72query := `SELECT t.tgname, pg_get_expr(t.tgqual, t.tgrelid, false)73FROM pg_catalog.pg_trigger t74WHERE t.tgrelid = $1 AND NOT t.tgisinternal`75rows, err := conn.Query(ctx, query, tableOID)76```
pgplex