Re: get a relations DDL server-side

"Jordan Deitch" <jd(at)rsa(dot)pub> writes:
> I would like to introduce the ability to get object DDL (server-side) by introducing a new function with roughly the following prototype:
> get_ddl(regclass)
> RETURNS text
> LANGUAGE C STRICT PARALLEL SAFE;

Umm ... "regclass" would only be appropriate for relations.

If you actually want to support more than one type of object with a single
function, you'll need two OIDs. Catalog's OID and object's OID are the
usual choices, per pg_describe_object() and similar functions.

I don't think "get_ddl" is a particularly apt function name, either.
It ignores the precedent of existing functions with essentially this
same functionality, such as pg_get_triggerdef(), pg_get_constraintdef(),
etc. One wonders why duplicate that existing functionality, so maybe
you should think about adding per-object-type functions instead of
trying to make one function to rule them all.

The larger reason why this doesn't exist already, BTW, is that we've
tended to find that it's not all that useful to get back a monolithic
chunk of DDL text for complicated objects such as tables. You should
provide a little more clarity as to what use-case you foresee, because
otherwise there are just a *whole* lot of things that aren't clear.
Some examples:

* Should the output include a CREATE COMMENT if the object has a comment?
* What about ownership and ACL (grants)?
* On tables, are foreign keys part of the table, or are they distinct
objects? (Hint: both answers can be correct depending on use-case)
* How about indexes, and do you want to treat constraint indexes
differently from other ones? (Constraint indexes *could* be made
part of the table's DDL, but other indexes need a separate CREATE)
* Do you need options, such as whether to pretty-print expressions?

You might also find it instructive to dig through the archives for
past discussions about moving more of pg_dump's logic into the server;
that's the area where this has come up over and over.

regards, tom lane