Re: Retail DDL

On Thu, 24 Jul 2025 at 16:26, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:

> Some years ago I gave a talk about $subject, but somehow it dropped off
> my radar. Now I'm looking at it again. The idea is to have a function
> (or set of functions) that would allow the user to get the DDL for any
> database object. Obviously we already have some functions for things
> like views and triggers, but most notably we don't have one for tables,
> something users have long complained about. I have been trying to think
> of a reasonable interface for a single function, where we would pass in,
> say, a catalog oid plus an object oid, and maybe some optional extra
> arguments. That seems a bit fragile, though. The alternative is that we
> have a separate function for each object type, e.g.
> pg_get_{objecttype}_ddl. I'm kinda leaning that way, but I'd like some
> sort of consensus before any work gets done.
>

Could you do anything with the reg* data types?

Have pg_get_ddl (regclass) return a CREATE TABLE or CREATE VIEW command, as
appropriate, while pg_get_ddl (regtype) would return CREATE TYPE etc.

They don't cover all the object types but might be helpful for at least
some cases.