Re: Retail DDL

Hi Andrew,

On Fri, Jul 25, 2025 at 1:56 AM 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.
>

We have something roughly in that category in the form of functions in
[1]. How about extending/contracting that interface to give us DDL of
the given object as well?

I mentioned contracting because the new interface may not handle
objects like columns which do not have an independent DDL. Or maybe we
could re-imagine DDL for such objects as ALTER .... ADD variant for
that object. For example, in case of a column it would be ALTER TABLE
... ADD COLUMN ....

[1] https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-INFO-OBJECT-TABLE

--
Best Wishes,
Ashutosh Bapat