mullinsms opened a new issue, #3971:
URL: https://github.com/apache/arrow-adbc/issues/3971
### 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)](#1-functions-scalar)
2. [Table-Returning Functions](#2-table-returning-functions)
3. [Aggregate Functions](#3-aggregate-functions)
4. [Stored Procedures (PG 12+)](#4-stored-procedures-pg-12)
5. [Function & Procedure Parameters](#5-function--procedure-parameters)
6. [Indexes](#6-indexes)
7. [Triggers](#7-triggers)
8. [Sequences](#8-sequences)
9. [View & Materialized View
Definitions](#9-view--materialized-view-definitions)
10. [Exclusion Constraints](#10-exclusion-constraints)
11. [Check Constraint Expressions](#11-check-constraint-expressions)
12. [Foreign Key Actions &
Deferrability](#12-foreign-key-actions--deferrability)
13. [Reverse Foreign Keys (Referenced
By)](#13-reverse-foreign-keys-referenced-by)
14. [Partition Hierarchy](#14-partition-hierarchy)
15. [Custom Types (Enums, Composites, Domains,
Ranges)](#15-custom-types-enums-composites-domains-ranges)
16. [Extensions](#16-extensions)
17. [Foreign Data Wrappers & Foreign
Servers](#17-foreign-data-wrappers--foreign-servers)
18. [Row-Level Security Policies](#18-row-level-security-policies)
19. [Rules](#19-rules)
20. [Database & Schema Properties](#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
```sql
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
```sql
-- 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
```sql
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
```sql
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`:
```sql
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
```sql
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:
```sql
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
```sql
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
```sql
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+):
```sql
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
```sql
-- 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
```sql
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
```sql
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
```sql
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
```sql
-- 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` |
```sql
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` |
```sql
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 |
```sql
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` |
```sql
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
```sql
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
```sql
-- 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
```sql
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
```sql
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
```sql
-- 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.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
