Re: Retail DDL

On 2025-07-25 Fr 4:34 AM, Álvaro Herrera wrote:
> On 2025-Jul-24, Andrew Dunstan wrote:
>
>> 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.
> Reproducing a table might need multiple commands. Do you intend to
> return a single string containing multiple semicolon-separated commands,
> or are you thinking in a RETURNS SETOF where each row contains a single
> command?

probably SETOF TEXT, but I'm open to persuasion. What would be best for
using it in some psql meta-commands?

> What about schema-qualification needed for elements in the commands? We
> have the option to schema-qualify everything, _or_ to depend on whether
> the schemas are in search_path, _or_ to schema-qualify nothing (which
> gives the user the chance to recreate in any schema by changing
> search_path).
>

Good question. Maybe that needs to be a function argument, say
defaulting to depending on the current search path.

>> 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.
> It looks like the discussion is leaning this way too. I think it's
> a reasonable choice.
>

Thanks. The only reason in my head against it was that it would expand
the number of visible functions, but I think that loses out against the
straightforwardness of this approach.

cheers

andrew

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