Re: Retail DDL

On 2025-08-13 We 10:29 PM, Ziga wrote:
> 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.



Interesting. I think there are good reasons to have this as builtin 
functions, though, not least that it would allow us to base some psql 
meta-commands on it, or possibly an SQL command (DESCRIBE ?). Builtin 
functions are also likely to be faster.


cheers


andrew


--
Andrew Dunstan
EDB: https://www.enterprisedb.com