Feature Request: [postgresql] Comprehensive Metadata Discovery for Data Development Tools

[mullinsms]

What feature or improvement would you like to see?

Summary

The ADBC PostgreSQL driver currently supports discovery of databases, schemas, tables (regular, views, materialized views, foreign, partitioned), columns, primary/foreign/unique/check constraints, and table statistics via GetStatistics(). However, PostgreSQL has a rich object model that data development tools need — functions, procedures, indexes, triggers, sequences, custom types, and more. This issue requests expanding metadata discovery to cover these gaps.

We build a collaborative data operations platform and currently query PostgreSQL system catalogs (pg_catalog) directly via JDBC for comprehensive metadata extraction. We are evaluating the ADBC driver as a modern Arrow-native replacement but cannot adopt it without coverage for the object categories listed below.

Each section describes what we need, why it matters, what metadata properties are required, and the exact pg_catalog queries to obtain it.

---

Table of Contents

1. Functions (Scalar)
2. Table-Returning Functions
3. Aggregate Functions
4. Stored Procedures (PG 12+)
5. Function & Procedure Parameters
6. Indexes
7. Triggers
8. Sequences
9. View & Materialized View Definitions
10. Exclusion Constraints
11. Check Constraint Expressions
12. Foreign Key Actions & Deferrability
13. Reverse Foreign Keys (Referenced By)
14. Partition Hierarchy
15. Custom Types (Enums, Composites, Domains, Ranges)
16. Extensions
17. Foreign Data Wrappers & Foreign Servers
18. Row-Level Security Policies
19. Rules
20. Database & Schema Properties

---

1. Functions (Scalar)

Motivation

User-defined functions are a core part of PostgreSQL's extensibility. They support SQL, PL/pgSQL, PL/Python, PL/Perl, C, and other languages. Functions can be overloaded (same name, different argument types). Data development tools need to enumerate them for autocomplete, signature help, documentation, and catalog browsing.

Required Metadata

Property	Description	Source
OID	Unique function identifier (needed for overload resolution)	pg_proc.oid
Schema	Containing schema	pg_namespace.nspname
Function name	Function identifier	pg_proc.proname
Owner	Function owner	pg_roles.rolname via pg_proc.proowner
Language	SQL, plpgsql, plpython3u, C, etc.	pg_language.lanname via pg_proc.prolang
Return type	Return data type	format_type(pg_proc.prorettype, NULL)
Volatility	VOLATILE, STABLE, IMMUTABLE	pg_proc.provolatile
Is strict	Returns NULL on NULL input	pg_proc.proisstrict
Security definer	Runs as definer vs invoker	pg_proc.prosecdef
Source code	Full CREATE FUNCTION statement	pg_get_functiondef(pg_proc.oid)
Description	User-provided comment	pg_description
Parallel safety	SAFE, RESTRICTED, UNSAFE	pg_proc.proparallel (PG 9.6+)
Cost	Estimated execution cost	pg_proc.procost
Rows	Estimated rows returned (set-returning)	pg_proc.prorows

How to Obtain

SELECT
    p.oid,
    p.proname AS name,
    n.nspname AS schema,
    r.rolname AS owner,
    l.lanname AS language,
    format_type(p.prorettype, NULL) AS return_type,
    p.provolatile,
    p.proisstrict,
    p.prosecdef,
    p.proparallel,
    p.procost,
    pg_get_functiondef(p.oid) AS definition,
    d.description
FROM pg_catalog.pg_proc p
INNER JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
INNER JOIN pg_catalog.pg_language l ON l.oid = p.prolang
LEFT JOIN pg_catalog.pg_roles r ON r.oid = p.proowner
LEFT JOIN pg_catalog.pg_description d
    ON d.objoid = p.oid
    AND d.classoid = 'pg_proc'::regclass
    AND d.objsubid = 0
WHERE n.nspname = $1
    AND p.prokind = 'f'       -- PG 11+; for older: NOT p.proisagg AND p.prorettype != 0
    AND NOT p.proretset        -- scalar only (not set-returning)
ORDER BY p.proname;

Overload Handling

PostgreSQL allows multiple functions with the same name but different argument types. The driver should:

1. Use pg_proc.oid as the unique identifier
2. Include argument type signatures to distinguish overloads
3. Group or label overloads clearly in results

---

2. Table-Returning Functions

Motivation

Set-returning functions (table functions) return tabular results and are used in FROM clauses. They need to be distinguished from scalar functions because they produce a result set with named columns.

Required Metadata

Same as scalar functions (section 1), plus:

Property	Description	Source
Returns set	Always true for table functions	pg_proc.proretset
Result columns	Column names and types	Derived from return type or OUT params

How to Identify

-- Table functions: prokind='f' AND proretset=true
WHERE p.prokind = 'f' AND p.proretset = true

Result columns come from:

• pg_proc.proallargtypes + pg_proc.proargmodes — OUT and TABLE parameters define result columns
• Or from the return composite type via pg_type + pg_attribute if returning a composite type

---

3. Aggregate Functions

Motivation

PostgreSQL has a rich aggregate function system supporting custom aggregates with transition functions, final functions, combine functions (for parallel aggregation), moving aggregates, and ordered-set/hypothetical-set aggregates. Tools need to display these alongside regular functions.

Required Metadata

Property	Description	Source
All function properties	Same as section 1	pg_proc
Aggregate kind	Normal, ordered-set, hypothetical-set	pg_aggregate.aggkind
Transition function	State transition function	pg_aggregate.aggtransfn
Final function	Finalization function	pg_aggregate.aggfinalfn
Combine function	Parallel combine function	pg_aggregate.aggcombinefn
Serialization function	Parallel state serialization	pg_aggregate.aggserialfn
Deserialization function	Parallel state deserialization	pg_aggregate.aggdeserialfn
Internal state type	Transition state data type	format_type(pg_aggregate.aggtranstype, NULL)
Initial value	Transition state initial value	pg_aggregate.agginitval
Sort operator	Sort operator for min/max aggregates	pg_operator.oprname via pg_aggregate.aggsortop
Moving transition function	For moving aggregates (window mode)	pg_aggregate.aggmtransfn
Moving inverse function	Inverse transition for moving aggregates	pg_aggregate.aggminvtransfn
Moving final function	Final function for moving aggregates	pg_aggregate.aggmfinalfn
Number of direct args	For ordered-set aggregates	pg_aggregate.aggnumdirectargs

How to Obtain

SELECT
    p.oid,
    p.proname,
    a.aggkind,
    a.aggtransfn::regproc AS transition_function,
    a.aggfinalfn::regproc AS final_function,
    a.aggcombinefn::regproc AS combine_function,
    a.aggserialfn::regproc AS serialization_function,
    a.aggdeserialfn::regproc AS deserialization_function,
    format_type(a.aggtranstype, NULL) AS internal_state_type,
    a.agginitval AS initial_value,
    a.aggkind,
    a.aggnumdirectargs,
    a.aggmtransfn::regproc AS moving_transition_function,
    a.aggminvtransfn::regproc AS moving_inverse_function,
    a.aggmfinalfn::regproc AS moving_final_function,
    format_type(a.aggmtranstype, NULL) AS moving_state_type,
    a.aggminitval AS moving_initial_value
FROM pg_catalog.pg_proc p
INNER JOIN pg_catalog.pg_aggregate a ON a.aggfnoid = p.oid
INNER JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname = $1
    AND p.prokind IN ('a', 'w')  -- aggregate or window
ORDER BY p.proname;

---

4. Stored Procedures (PG 12+)

Motivation

PostgreSQL 12 introduced true stored procedures (distinct from functions) with transaction control capabilities (COMMIT/ROLLBACK). Tools need to distinguish procedures from functions and display them appropriately.

Required Metadata

Same as scalar functions (section 1), but:

Property	Description	Source
Procedure kind	Always 'p'	pg_proc.prokind = 'p'
Support for CALL	Invoked via CALL, not SELECT	Implicit from kind
OUT parameters	PG 14+ supports OUT args in procedures	pg_proc.proargmodes

How to Identify

WHERE p.prokind = 'p'  -- PG 11+ only

Version Considerations

• PG < 11: No prokind column. Use proisagg and return type heuristics
• PG 11: prokind added but procedures are PG 12+
• PG 14: OUT parameters in procedures fully supported

---

5. Function & Procedure Parameters

Motivation

Parameter metadata enables signature help, call template generation, and documentation. PostgreSQL functions support IN, OUT, INOUT, and VARIADIC parameter modes.

Required Metadata

Property	Description	Source
Parameter name	May be unnamed	pg_proc.proargnames[i]
Parameter type	Data type OID	pg_proc.proargtypes[i] (IN only) or pg_proc.proallargtypes[i] (all)
Parameter mode	IN, OUT, INOUT, VARIADIC, TABLE	pg_proc.proargmodes[i]
Ordinal position	1-based	Array index
Has default	Whether parameter has a default	pg_proc.pronargdefaults > 0

How to Obtain

Parameters are stored as arrays on pg_proc:

SELECT
    p.oid,
    p.proname,
    p.proargtypes,          -- IN argument type OIDs (oidvector)
    p.proallargtypes,       -- All argument type OIDs (including OUT)
    p.proargnames,          -- Argument names (text[])
    p.proargmodes,          -- Argument modes: i=IN, o=OUT, b=INOUT, v=VARIADIC, t=TABLE
    p.pronargdefaults,      -- Number of arguments with defaults
    pg_get_function_arguments(p.oid) AS argument_list,       -- Formatted argument list
    pg_get_function_result(p.oid) AS result_type             -- Formatted return type
FROM pg_catalog.pg_proc p
WHERE p.oid = $1;

The arrays must be unnested in application code or via UNNEST():

• proargtypes only includes IN parameters
• proallargtypes includes all parameters (IN, OUT, INOUT, VARIADIC, TABLE) — NULL if only IN params
• proargmodes — NULL if all parameters are IN
• proargnames — NULL entries for unnamed parameters

---

6. Indexes

Motivation

Index metadata is critical for query performance analysis, database optimization, and schema documentation. PostgreSQL supports multiple access methods (btree, hash, gin, gist, spgist, brin) with rich options (partial indexes, expression indexes, included columns, etc.).

Required Metadata

Property	Description	Source
Index name	Index identifier	pg_class.relname (index class)
Table name	Indexed table	pg_class.relname (table class)
Access method	btree, hash, gin, gist, spgist, brin	pg_am.amname
Is unique	Whether the index enforces uniqueness	pg_index.indisunique
Is primary	Whether it backs the primary key	pg_index.indisprimary
Is clustered	Whether the table is clustered on this index	pg_index.indisclustered
Is valid	Whether the index is valid (concurrent build may leave invalid)	pg_index.indisvalid
Is exclusion	Whether it backs an exclusion constraint	pg_index.indisexclusion
Column count	Number of index columns	pg_class.relnatts
Column expressions	Expression per column position	pg_get_indexdef(indexrelid, col, true)
Column options	ASC/DESC, NULLS FIRST/LAST per column	pg_index.indoption
Partial predicate	WHERE clause for partial indexes	pg_get_expr(pg_index.indpred, indrelid)
Included columns	Columns stored but not indexed (PG 11+)	Columns beyond indnkeyatts
Full DDL	Complete CREATE INDEX statement	pg_get_indexdef(indexrelid)
Tablespace	Tablespace storing the index	pg_indexes.tablespace
Description	User-provided comment	pg_description
Size	Index size in bytes	pg_relation_size(indexrelid)

How to Obtain

SELECT
    ic.relname AS index_name,
    tc.relname AS table_name,
    am.amname AS access_method,
    i.indisunique AS is_unique,
    i.indisprimary AS is_primary,
    i.indisclustered AS is_clustered,
    i.indisvalid AS is_valid,
    i.indisexclusion AS is_exclusion,
    ic.relnatts AS column_count,
    i.indnkeyatts AS key_column_count,  -- PG 11+
    i.indkey AS column_numbers,
    i.indoption AS column_options,
    pg_get_expr(i.indpred, i.indrelid) AS predicate,
    pg_get_indexdef(i.indexrelid) AS definition,
    pg_indexes.tablespace,
    d.description,
    pg_relation_size(i.indexrelid) AS size_bytes
FROM pg_catalog.pg_index i
INNER JOIN pg_catalog.pg_class ic ON ic.oid = i.indexrelid
INNER JOIN pg_catalog.pg_class tc ON tc.oid = i.indrelid
INNER JOIN pg_catalog.pg_namespace n ON n.oid = tc.relnamespace
INNER JOIN pg_catalog.pg_am am ON am.oid = ic.relam
LEFT JOIN pg_catalog.pg_indexes
    ON pg_indexes.schemaname = n.nspname
    AND pg_indexes.tablename = tc.relname
    AND pg_indexes.indexname = ic.relname
LEFT JOIN pg_catalog.pg_description d
    ON d.objoid = i.indexrelid
    AND d.classoid = 'pg_class'::regclass
WHERE n.nspname = $1 AND tc.relname = $2
ORDER BY ic.relname;

Per-column expressions:

SELECT pg_get_indexdef(indexrelid, column_number, true) AS column_expression

---

7. Triggers

Motivation

Triggers are essential for understanding data modification side effects, audit logging, constraint enforcement, and event-driven logic. Tools need to display trigger metadata for schema comprehension and debugging.

Required Metadata

Property	Description	Source
Trigger name	Trigger identifier	pg_trigger.tgname
Table name	Table the trigger is on	pg_class.relname
Timing	BEFORE, AFTER, INSTEAD OF	Decoded from pg_trigger.tgtype bits
Events	INSERT, UPDATE, DELETE, TRUNCATE	Decoded from pg_trigger.tgtype bits
Row/statement level	FOR EACH ROW vs FOR EACH STATEMENT	Decoded from pg_trigger.tgtype bit
Function name	Trigger function called	pg_proc.proname via pg_trigger.tgfoid
Function schema	Schema of trigger function	pg_namespace.nspname
Enabled state	O=origin, D=disabled, R=replica, A=always	pg_trigger.tgenabled
Deferrable	Whether the trigger is deferrable	pg_trigger.tgdeferrable
Initially deferred	Default deferral state	pg_trigger.tginitdeferred
Is internal	System-generated (for constraint FK triggers)	pg_trigger.tgisinternal
When condition	Conditional execution expression	pg_get_triggerdef() contains WHEN clause
Columns	Specific columns for UPDATE OF	pg_trigger.tgattr
Old table alias	REFERENCING OLD TABLE AS name	pg_trigger.tgoldtable
New table alias	REFERENCING NEW TABLE AS name	pg_trigger.tgnewtable
Constraint name	For constraint triggers	pg_trigger.tgconstrname
Referenced table	For constraint triggers (FK table)	pg_class.relname via pg_trigger.tgconstrrelid
Parent trigger	For partition triggers (PG 13+)	pg_trigger.tgparentid
Full DDL	Complete CREATE TRIGGER statement	pg_get_triggerdef(pg_trigger.oid)
Description	User-provided comment	pg_description

How to Obtain

SELECT
    t.oid,
    t.tgname AS trigger_name,
    tc.relname AS table_name,
    pg_get_triggerdef(t.oid) AS definition,
    t.tgtype,
    t.tgenabled,
    t.tgdeferrable,
    t.tginitdeferred,
    t.tgisinternal,
    t.tgattr AS update_columns,
    t.tgoldtable AS old_table_alias,
    t.tgnewtable AS new_table_alias,
    f.proname AS function_name,
    fn.nspname AS function_schema,
    t.tgconstrname AS constraint_name,
    rt.relname AS referenced_table,
    rn.nspname AS referenced_schema,
    pt.tgname AS parent_trigger_name,
    d.description
FROM pg_catalog.pg_trigger t
INNER JOIN pg_catalog.pg_class tc ON tc.oid = t.tgrelid
INNER JOIN pg_catalog.pg_namespace tn ON tn.oid = tc.relnamespace
LEFT JOIN pg_catalog.pg_proc f ON f.oid = t.tgfoid
LEFT JOIN pg_catalog.pg_namespace fn ON fn.oid = f.pronamespace
LEFT JOIN pg_catalog.pg_class rt ON rt.oid = t.tgconstrrelid
LEFT JOIN pg_catalog.pg_namespace rn ON rn.oid = rt.relnamespace
LEFT JOIN pg_catalog.pg_trigger pt ON pt.oid = t.tgparentid
LEFT JOIN pg_catalog.pg_description d
    ON d.objoid = t.oid
    AND d.classoid = 'pg_trigger'::regclass
WHERE tn.nspname = $1 AND tc.relname = $2
ORDER BY t.tgname;

Decoding tgtype

The tgtype column is a bitmask:

• Bit 0 (1): ROW-level (vs STATEMENT)
• Bit 1 (2): BEFORE (vs AFTER)
• Bit 2 (4): INSERT
• Bit 3 (8): DELETE
• Bit 4 (16): UPDATE
• Bit 5 (32): TRUNCATE
• Bit 6 (64): INSTEAD OF

---

8. Sequences

Motivation

Sequences generate unique numeric identifiers and are referenced by SERIAL/BIGSERIAL columns and GENERATED AS IDENTITY columns. Tools need to display sequence properties for schema comprehension.

Required Metadata

Property	Description	Source
Sequence name	Sequence identifier	pg_class.relname (relkind='S')
Schema	Containing schema	pg_namespace.nspname
Data type	smallint, integer, bigint	format_type(pg_sequence.seqtypid, NULL)
Start value	Initial value	pg_sequence.seqstart
Increment	Step value	pg_sequence.seqincrement
Minimum value	Lower bound	pg_sequence.seqmin
Maximum value	Upper bound	pg_sequence.seqmax
Cache size	Pre-allocated values	pg_sequence.seqcache
Cycle	Whether sequence wraps around	pg_sequence.seqcycle
Last value	Most recently used value	pg_sequences.last_value
Owner	Sequence owner	pg_roles.rolname
Owned by column	Owning column (for SERIAL)	pg_depend → table.column
Description	User-provided comment	pg_description

How to Obtain

SELECT
    c.relname AS sequence_name,
    n.nspname AS schema_name,
    format_type(s.seqtypid, NULL) AS data_type,
    s.seqstart AS start_value,
    s.seqincrement AS increment_by,
    s.seqmin AS min_value,
    s.seqmax AS max_value,
    s.seqcache AS cache_size,
    s.seqcycle AS is_cycle,
    r.rolname AS owner,
    d.description
FROM pg_catalog.pg_sequence s
INNER JOIN pg_catalog.pg_class c ON c.oid = s.seqrelid
INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
LEFT JOIN pg_catalog.pg_description d
    ON d.objoid = c.oid
    AND d.classoid = 'pg_class'::regclass
    AND d.objsubid = 0
WHERE n.nspname = $1
ORDER BY c.relname;

For current last value (requires pg_sequences view, PG 10+):

SELECT last_value FROM pg_sequences WHERE schemaname = $1 AND sequencename = $2;

---

9. View & Materialized View Definitions

Motivation

View SQL is essential for understanding data transformations, debugging query behavior, migration, and documentation. The driver currently returns views as table objects but does not include their defining query.

Required Metadata

Property	Description	Source
View name	View identifier	pg_class.relname
Definition	The SELECT statement	pg_views.definition or pg_get_viewdef()
Is materialized	Regular view vs materialized	pg_class.relkind ('v' vs 'm')
Check option	CASCADED, LOCAL, or NONE	pg_views.definition contains WITH CHECK OPTION
Tablespace	Storage location (materialized views only)	pg_tablespace.spcname

How to Obtain

-- Views
SELECT viewname, definition
FROM pg_catalog.pg_views
WHERE schemaname = $1;

-- Materialized views
SELECT c.relname, pg_get_viewdef(c.oid, true) AS definition,
       t.spcname AS tablespace
FROM pg_catalog.pg_class c
INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_tablespace t ON t.oid = c.reltablespace
WHERE n.nspname = $1 AND c.relkind = 'm';

Suggested Approach

Add an optional table_definition (utf8) field to TABLE_SCHEMA in the GetObjects result, populated for views and materialized views.

---

10. Exclusion Constraints

Motivation

Exclusion constraints (EXCLUDE USING) are a PostgreSQL-specific constraint type that prevents overlapping values (commonly used with range types and GiST indexes). The driver currently supports PRIMARY KEY, FOREIGN KEY, UNIQUE, and CHECK constraints but not exclusion constraints.

Required Metadata

Property	Description	Source
Constraint name	Constraint identifier	pg_constraint.conname
Constraint type	EXCLUDE	pg_constraint.contype = 'x'
Column names	Columns in the constraint	pg_constraint.conkey → pg_attribute.attname
Access method	Index access method (typically gist)	pg_am.amname via index
Full definition	Complete constraint SQL	pg_get_constraintdef(oid, true)
Index name	Backing index	pg_class.relname via pg_constraint.conindid

How to Obtain

SELECT
    con.conname AS constraint_name,
    pg_get_constraintdef(con.oid, true) AS definition,
    ic.relname AS index_name,
    am.amname AS access_method
FROM pg_catalog.pg_constraint con
INNER JOIN pg_catalog.pg_class tc ON tc.oid = con.conrelid
INNER JOIN pg_catalog.pg_namespace n ON n.oid = con.connamespace
LEFT JOIN pg_catalog.pg_class ic ON ic.oid = con.conindid
LEFT JOIN pg_catalog.pg_am am ON am.oid = ic.relam
WHERE n.nspname = $1 AND tc.relname = $2
    AND con.contype = 'x';

Note

The existing ADBC CONSTRAINT_SCHEMA would need a new constraint_type value ("EXCLUDE") and ideally a constraint_expression field to carry the full definition.

---

11. Check Constraint Expressions

Motivation

The driver currently returns check constraints with column names but not the actual expression. The expression is the most important part — it tells users what validation rule is being enforced (e.g., price > 0, status IN ('active', 'inactive')).

Required Metadata

Property	Description	Source
Expression	The CHECK expression text	pg_get_constraintdef(oid, true)

How to Obtain

SELECT
    con.conname,
    pg_get_constraintdef(con.oid, true) AS definition
FROM pg_catalog.pg_constraint con
WHERE con.contype = 'c' AND con.conrelid = $1::regclass;

Returns text like: CHECK ((price > 0)) or CHECK ((status = ANY (ARRAY['active'::text, 'inactive'::text]))).

Suggested Approach

Add a constraint_expression (utf8) field to CONSTRAINT_SCHEMA in the ADBC spec, or encode the expression in the existing constraint_column_names field as a single-element list.

---

12. Foreign Key Actions & Deferrability

Motivation

The driver currently returns foreign key references (referenced table and columns) but not the ON UPDATE/ON DELETE actions or deferrability settings. These are essential for understanding cascading behavior and constraint timing.

Required Metadata

Property	Description	Source
On update action	NO ACTION, RESTRICT, CASCADE, SET NULL, SET DEFAULT	pg_constraint.confupdtype
On delete action	NO ACTION, RESTRICT, CASCADE, SET NULL, SET DEFAULT	pg_constraint.confdeltype
Match type	FULL, PARTIAL, SIMPLE	pg_constraint.confmatchtype
Is deferrable	Whether the constraint can be deferred	pg_constraint.condeferrable
Initially deferred	Default deferral state	pg_constraint.condeferred

Action Code Mapping

Code	Action
a	NO ACTION
r	RESTRICT
c	CASCADE
n	SET NULL
d	SET DEFAULT

How to Obtain

These columns are already available on pg_constraint, which the driver already queries for FK constraints. They just need to be included in the result.

---

13. Reverse Foreign Keys (Referenced By)

Motivation

When inspecting a table, users need to see not only which tables it references but also which tables reference it. This is essential for impact analysis — understanding what would break if a table or column is modified.

Required Metadata

Property	Description
Referencing schema	Schema of the table holding the FK
Referencing table	Table that holds the FK
FK constraint name	Foreign key constraint name
Referencing columns	Columns in the referencing table
Referenced columns	Columns in the target table

How to Obtain

SELECT
    rn.nspname AS referencing_schema,
    rc.relname AS referencing_table,
    con.conname AS constraint_name,
    con.conkey AS referencing_column_nums,
    con.confkey AS referenced_column_nums
FROM pg_catalog.pg_constraint con
INNER JOIN pg_catalog.pg_class rc ON rc.oid = con.conrelid
INNER JOIN pg_catalog.pg_namespace rn ON rn.oid = rc.relnamespace
WHERE con.contype = 'f'
    AND con.confrelid = $1::regclass  -- the target table OID
ORDER BY rn.nspname, rc.relname, con.conname;

---

14. Partition Hierarchy

Motivation

PostgreSQL 10+ supports declarative partitioning. Tools need to display the partition hierarchy — the parent partitioned table and its child partitions, including partition bounds and strategy.

Required Metadata

Property	Description	Source
Parent table	Partitioned table name	pg_class.relname (relkind='p')
Partition strategy	RANGE, LIST, HASH	pg_partitioned_table.partstrat
Partition key	Key columns/expressions	pg_partitioned_table.partattrs + pg_partitioned_table.partexprs
Child partitions	List of partition table names	pg_inherits.inhrelid → pg_class.relname
Partition bounds	Bound specification per child	pg_get_expr(pg_class.relpartbound, oid)

How to Obtain

-- Partition strategy and key
SELECT
    pt.partstrat,
    pt.partnatts,
    pt.partattrs,
    pg_get_partkeydef(c.oid) AS partition_key_def
FROM pg_catalog.pg_partitioned_table pt
INNER JOIN pg_catalog.pg_class c ON c.oid = pt.partrelid
WHERE c.oid = $1::regclass;

-- Child partitions with bounds
SELECT
    child.relname AS partition_name,
    pg_get_expr(child.relpartbound, child.oid) AS partition_bound
FROM pg_catalog.pg_inherits i
INNER JOIN pg_catalog.pg_class child ON child.oid = i.inhrelid
WHERE i.inhparent = $1::regclass
ORDER BY child.relname;

---

15. Custom Types (Enums, Composites, Domains, Ranges)

Motivation

PostgreSQL's extensible type system is a major feature. Custom types appear in column definitions, function signatures, and domain constraints. Tools need to enumerate and describe them.

Required Metadata

Enum Types

Property	Description	Source
Type name	Enum type name	pg_type.typname (typtype='e')
Schema	Containing schema	pg_namespace.nspname
Labels	Ordered list of enum values	pg_enum.enumlabel ordered by enumsortorder

SELECT t.typname, n.nspname, e.enumlabel
FROM pg_catalog.pg_type t
INNER JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
INNER JOIN pg_catalog.pg_enum e ON e.enumtypid = t.oid
WHERE n.nspname = $1 AND t.typtype = 'e'
ORDER BY t.typname, e.enumsortorder;

Composite Types

Property	Description	Source
Type name	Composite type name	pg_type.typname (typtype='c')
Fields	Ordered list of field names and types	pg_attribute for pg_type.typrelid

SELECT t.typname, a.attname, format_type(a.atttypid, a.atttypmod) AS field_type, a.attnum
FROM pg_catalog.pg_type t
INNER JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
INNER JOIN pg_catalog.pg_attribute a ON a.attrelid = t.typrelid
WHERE n.nspname = $1 AND t.typtype = 'c'
    AND a.attnum > 0 AND NOT a.attisdropped
    AND NOT EXISTS (SELECT 1 FROM pg_class c WHERE c.oid = t.typrelid AND c.relkind IN ('r','v','m','p','f'))
ORDER BY t.typname, a.attnum;

Domain Types

Property	Description	Source
Domain name	Domain type name	pg_type.typname (typtype='d')
Base type	Underlying data type	format_type(pg_type.typbasetype, pg_type.typtypmod)
Not null	Whether domain is NOT NULL	pg_type.typnotnull
Default	Default expression	pg_type.typdefault
Check constraints	Domain constraints	pg_constraint for domain OID

SELECT t.typname, format_type(t.typbasetype, t.typtypmod) AS base_type,
       t.typnotnull, t.typdefault,
       pg_get_constraintdef(con.oid) AS constraint_def
FROM pg_catalog.pg_type t
INNER JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
LEFT JOIN pg_catalog.pg_constraint con ON con.contypid = t.oid
WHERE n.nspname = $1 AND t.typtype = 'd'
ORDER BY t.typname;

Range Types

Property	Description	Source
Range name	Range type name	pg_type.typname (typtype='r')
Subtype	Element type of range	format_type(pg_range.rngsubtype, NULL)
Canonical function	Canonicalization function	pg_range.rngcanonical
Subtype diff function	Difference function	pg_range.rngsubdiff

SELECT t.typname, format_type(r.rngsubtype, NULL) AS subtype,
       r.rngcanonical::regproc, r.rngsubdiff::regproc
FROM pg_catalog.pg_type t
INNER JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
INNER JOIN pg_catalog.pg_range r ON r.rngtypid = t.oid
WHERE n.nspname = $1
ORDER BY t.typname;

---

16. Extensions

Motivation

Extensions package functions, types, operators, and other objects into installable modules. Tools need to show installed extensions for schema comprehension and dependency tracking.

Required Metadata

Property	Description	Source
Extension name	Extension identifier	pg_extension.extname
Version	Installed version	pg_extension.extversion
Schema	Installation schema	pg_namespace.nspname via pg_extension.extnamespace
Relocatable	Whether extension can be moved	pg_extension.extrelocatable
Description	Extension description	pg_description or pg_available_extensions.comment

How to Obtain

SELECT
    e.extname,
    e.extversion,
    n.nspname AS schema,
    e.extrelocatable,
    ae.comment AS description
FROM pg_catalog.pg_extension e
INNER JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace
LEFT JOIN pg_catalog.pg_available_extensions ae ON ae.name = e.extname
ORDER BY e.extname;

---

17. Foreign Data Wrappers & Foreign Servers

Motivation

Foreign data wrappers (FDWs) enable access to external data sources. Tools need to display FDW and foreign server configurations to understand external data integration.

Required Metadata

Foreign Data Wrappers

Property	Description	Source
FDW name	Wrapper identifier	pg_foreign_data_wrapper.fdwname
Handler function	FDW handler	pg_proc.proname via fdwhandler
Validator function	FDW validator	pg_proc.proname via fdwvalidator
Options	Key-value options	pg_foreign_data_wrapper.fdwoptions
Owner	FDW owner	pg_roles.rolname

Foreign Servers

Property	Description	Source
Server name	Server identifier	pg_foreign_server.srvname
FDW name	Associated wrapper	pg_foreign_data_wrapper.fdwname
Type	Server type string	pg_foreign_server.srvtype
Version	Server version string	pg_foreign_server.srvversion
Options	Connection options (host, port, dbname, etc.)	pg_foreign_server.srvoptions
Owner	Server owner	pg_roles.rolname

How to Obtain

-- Foreign data wrappers
SELECT fdw.fdwname, h.proname AS handler, v.proname AS validator,
       fdw.fdwoptions, r.rolname AS owner
FROM pg_catalog.pg_foreign_data_wrapper fdw
LEFT JOIN pg_catalog.pg_proc h ON h.oid = fdw.fdwhandler
LEFT JOIN pg_catalog.pg_proc v ON v.oid = fdw.fdwvalidator
LEFT JOIN pg_catalog.pg_roles r ON r.oid = fdw.fdwowner;

-- Foreign servers
SELECT s.srvname, fdw.fdwname, s.srvtype, s.srvversion,
       s.srvoptions, r.rolname AS owner
FROM pg_catalog.pg_foreign_server s
INNER JOIN pg_catalog.pg_foreign_data_wrapper fdw ON fdw.oid = s.srvfdw
LEFT JOIN pg_catalog.pg_roles r ON r.oid = s.srvowner;

---

18. Row-Level Security Policies

Motivation

Row-level security (RLS) policies control which rows are visible or modifiable by different roles. Tools should display these policies for security audit and documentation.

Required Metadata

Property	Description	Source
Policy name	Policy identifier	pg_policy.polname
Table name	Table the policy applies to	pg_class.relname
Command	ALL, SELECT, INSERT, UPDATE, DELETE	pg_policy.polcmd
Permissive/Restrictive	Policy type	pg_policy.polpermissive
Roles	Roles the policy applies to	pg_policy.polroles
USING expression	Visibility filter	pg_get_expr(pg_policy.polqual, polrelid)
WITH CHECK expression	Modification filter	pg_get_expr(pg_policy.polwithcheck, polrelid)

How to Obtain

SELECT
    pol.polname AS policy_name,
    c.relname AS table_name,
    pol.polcmd AS command,
    pol.polpermissive AS is_permissive,
    pol.polroles AS roles,
    pg_get_expr(pol.polqual, pol.polrelid) AS using_expression,
    pg_get_expr(pol.polwithcheck, pol.polrelid) AS with_check_expression
FROM pg_catalog.pg_policy pol
INNER JOIN pg_catalog.pg_class c ON c.oid = pol.polrelid
INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = $1
ORDER BY c.relname, pol.polname;

---

19. Rules

Motivation

Rules rewrite queries before execution. While triggers are generally preferred for most use cases, rules are used for view UPDATE/INSERT/DELETE support and some legacy patterns. Tools should display them when present.

Required Metadata

Property	Description	Source
Rule name	Rule identifier	pg_rewrite.rulename
Table/view name	Object the rule applies to	pg_class.relname
Event	SELECT, INSERT, UPDATE, DELETE	pg_rewrite.ev_type
Is instead	INSTEAD rule vs ALSO	pg_rewrite.is_instead
Definition	Full rule SQL	pg_get_ruledef(oid)

How to Obtain

SELECT
    rw.rulename,
    c.relname AS table_name,
    rw.ev_type,
    rw.is_instead,
    pg_get_ruledef(rw.oid) AS definition
FROM pg_catalog.pg_rewrite rw
INNER JOIN pg_catalog.pg_class c ON c.oid = rw.ev_class
INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = $1
    AND rw.rulename != '_RETURN'  -- exclude default view rules
ORDER BY c.relname, rw.rulename;

---

20. Database & Schema Properties

Motivation

The driver currently returns database and schema names but no additional properties. Tools need descriptions, owners, and configuration for context display.

Required Metadata

Databases

Property	Description	Source
Database name	Database identifier	pg_database.datname
Owner	Database owner	pg_roles.rolname via pg_database.datdba
Encoding	Character encoding	pg_encoding_to_char(pg_database.encoding)
Collation	Default collation	pg_database.datcollate
Tablespace	Default tablespace	pg_tablespace.spcname via pg_database.dattablespace
Connection limit	Max concurrent connections	pg_database.datconnlimit
Description	User-provided comment	pg_shdescription

Schemas

Property	Description	Source
Schema name	Schema identifier	pg_namespace.nspname
Owner	Schema owner	pg_roles.rolname via pg_namespace.nspowner
Description	User-provided comment	pg_description

How to Obtain

-- Databases
SELECT d.datname, r.rolname AS owner,
       pg_encoding_to_char(d.encoding) AS encoding,
       d.datcollate, t.spcname AS tablespace,
       d.datconnlimit, sd.description
FROM pg_catalog.pg_database d
LEFT JOIN pg_catalog.pg_roles r ON r.oid = d.datdba
LEFT JOIN pg_catalog.pg_tablespace t ON t.oid = d.dattablespace
LEFT JOIN pg_catalog.pg_shdescription sd
    ON sd.objoid = d.oid AND sd.classoid = 'pg_database'::regclass
WHERE d.datname != 'template0';

-- Schemas
SELECT n.nspname, r.rolname AS owner, d.description
FROM pg_catalog.pg_namespace n
LEFT JOIN pg_catalog.pg_roles r ON r.oid = n.nspowner
LEFT JOIN pg_catalog.pg_description d
    ON d.objoid = n.oid AND d.classoid = 'pg_namespace'::regclass
WHERE n.nspname NOT LIKE 'pg_temp_%' AND n.nspname NOT LIKE 'pg_toast_%';

---

Summary Priority Table

#	Category	pg_catalog Source	Impact
1	Scalar Functions	pg_proc (prokind='f')	High — core SQL development
2	Table Functions	pg_proc (proretset=true)	High — SQL development
3	Aggregate Functions	pg_proc + pg_aggregate	High — SQL development
4	Stored Procedures (PG 12+)	pg_proc (prokind='p')	High — SQL development
5	Function/Procedure Parameters	pg_proc arrays	High — signature help
6	Indexes	pg_index + pg_am	High — performance tuning
7	Triggers	pg_trigger	High — schema comprehension
8	Sequences	pg_sequence	Medium — schema browsing
9	View/MV Definitions	pg_views / pg_get_viewdef()	High — SQL development
10	Exclusion Constraints	pg_constraint (contype='x')	Medium — constraint completeness
11	Check Constraint Expressions	pg_get_constraintdef()	Medium — constraint detail
12	FK Actions & Deferrability	pg_constraint columns	Medium — constraint detail
13	Reverse Foreign Keys	pg_constraint (reversed)	Medium — impact analysis
14	Partition Hierarchy	pg_partitioned_table + pg_inherits	Medium — PG 10+
15	Custom Types	pg_type + pg_enum + pg_range	Medium — type system
16	Extensions	pg_extension	Low — admin context
17	Foreign Data Wrappers	pg_foreign_data_wrapper + pg_foreign_server	Low — external data
18	Row-Level Security	pg_policy	Low — security audit
19	Rules	pg_rewrite	Low — legacy feature
20	Database & Schema Properties	pg_database + pg_namespace	Low — enriched context

Note on GetStatistics

The ADBC PostgreSQL driver already implements GetStatistics() using pg_stats, which is a capability the other ADBC drivers (Databricks, Snowflake, BigQuery) lack. This is a strength of the PostgreSQL driver.

We're happy to provide further details on any of these categories.

[lidavidm]

I'm not sure about extensions, FDWs, and rules. Row-level security policies doesn't seem to overlap much with BigQuery so I'm not sure if there's any value in a general method there either. Other things have been converted into feature requests (see mentioned issues above), albeit I still need to sketch out what to do about data types.