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