Hi Andrew,
On 24/07/2025 22:26, Andrew Dunstan 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.
$subject has been appearing on the lists every now and then, without
much great success so far.
I have endeavored to implement such a thing as ddlx postgres extension,
https://github.com/lacanoid/pgddl
The endeavor is somewhat far gone now already. Apparently the extension
is used by some people. It probably has some interesting features. It
needs wider and more testing. I use it a lot. It tries to address some
of the issues on $subject expressed on the lists.
It is implemented as plain SQL functions. There are currently 89
functions with obvious names, one for each postgres object type, as well
as functions to assemble smaller pieces together and such. I think it
implements a rather nice SQL API, also handling some of the things
discussed here.
Of particular note is using oids only (no classid) to specify objects. I
used believe that oid are unique across a postgres database for catalog
objects, but since postgres 14 this no longer the case, see:
https://github.com/lacanoid/pgddl/issues/25 . I don't know if this is
intentional or not. In practice, it does not hinder usage.
