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



Reply via email to