pg_describe

A PostgreSQL function that replicates psql's \d and \d+ commands in pure SQL - returning the relation descriptions as a regular result set you can query from any SQL client.

Why?

Postgres has \d name in psql, but you can't call it over a regular SQL connection - a GUI, a notebook, an application, or anywhere else that just speaks SQL. pg_describe fills that gap with the same output, the same pattern syntax, and the same verbose mode.

Installation

psql -d mydb -f pg_describe.sql

Usage

-- List all visible user relations  (\d)
select * from pg_describe();

-- Describe a single relation
select * from pg_describe('public.orders');
select * from pg_describe('orders');           -- visibility search, like \d

-- Wildcard patterns  (\d cust*)
select * from pg_describe('cust*');

-- Schema patterns
select * from pg_describe('hr.*');             -- all objects in hr schema
select * from pg_describe('*.employees');      -- employees in any schema
select * from pg_describe('*.*');              -- everything in the database

-- Verbose mode  (\d+)
select * from pg_describe('orders', true);

Screenshot

Pattern rules

Pattern syntax mirrors psql:

Pattern	Meaning
*	any sequence of characters
?	any single character
"quoted"	case-sensitive literal (* ? . treated as plain chars)
schema.name	match by schema; no visibility filter
name (no dot)	match visible relations only
*.*	all objects in all schemas (including system)

Unquoted characters are folded to lower-case before matching, so ORDERS, Orders, and orders all find public.orders.

Output

Four text columns (a, b, c, d). With no argument, returns a listing:

a	b	c	d
schema	relation name	type	owner

With a pattern, returns full descriptions. Sections vary by relation type:

Section	a	b	c	d
Title	Table "schema.name" etc.
Columns	name	type	not null / ``	default
Indexes		definition
Check constraints		name	definition
Foreign-key constraints		name	definition
Referenced by	table	constraint	definition
Triggers	name	timing + level	events	function
Partition key (partitioned)	definition
Server (foreign table)	server name
Sequence properties	property	value

Verbose mode (true) adds:

Section	a	b	c	d
Column details	name	storage	stats target	comment
Not-null constraints		name	definition
Definition (views)	SQL line
Access method	Access method: heap

Relation types

pg_describe handles all eight relation types that \d covers:

Type	relkind
Table	r
Partitioned table	p
View	v
Materialized view	m
Sequence	S
Index	i
Foreign table	f
Composite type	c

Requirements

PostgreSQL 14-18 or later, no extensions are required.

Testing

Requires pgTAP. Load the schema and run:

make setup
make test

Linting

Requires plpgsql_check. Analyses the PL/pgSQL function bodies for type mismatches, unused variables, and invalid embedded SQL:

make lint

Examples

The tests/test-schema.sql file contains a sample schema (two schemas, seven tables, three views, three triggers, a partitioned table, a foreign table, and a composite type):

psql -d mydb -f pg_describe.sql
psql -d mydb -f tests/test-schema.sql