Re: Retail DDL

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.