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