depesz

PostgreSQL 19 introduces three new SQL-callable functions: pg_get_database_ddl(), pg_get_role_ddl(), and pg_get_tablespace_ddl(). These functions return the DDL statements needed to recreate databases, roles, and tablespaces respectively — essentially exposing what pg_dump would produce as queryable SQL functions. Each function accepts optional parameters like 'pretty' for formatted output and 'owner' to include ownership info. Notable caveat: pg_get_role_ddl() intentionally omits password information. The post demonstrates practical usage with examples for each function, including how to iterate over all tablespaces or roles using catalog queries.

ddl() functions – select * from depesz;