Re: use pg_get_functiondef() in pg_dump

Greetings,

* Corey Huinker (corey(dot)huinker(at)gmail(dot)com) wrote:
> > I'm sure there's a lot of folks who'd like to see more of the logic we
> > have in pg_dump for building objects from the catalog available to more
> > tools through libpgcommon- psql being one of the absolute first
> > use-cases for exactly that (there's certainly no shortage of people
> > who've asked how they can get a CREATE TABLE statement for a table by
> > using psql...).
>
> I count myself among those folks (see
> https://www.postgresql.org/message-id/CADkLM%3DfxfsrHASKk_bY_A4uomJ1Te5MfGgD_rwwQfV8wP68ewg%40mail.gmail.com
> for
> discussion of doing DESCRIBE and SHOW CREATE-ish functions either on server
> side or client side).
>
> I'm all for having this as "just" as set of pg_get_*def functions, because
> they allow for the results to be used in queries. Granted, the shape of the
> result set may not be stable, but that's the sort of thing we can warn for
> the same way we have warnings for changes to pg_stat_activity. At that
> point any DESCRIBE/SHOW CREATE server side functions essentially become
> just shells around the pg_get_*def(), with no particular requirement to
> make those new commands work inside a SELECT.

Another advantage of having this in libpgcommon is that the backend
*and* the frontend could then use it.

> Would it be totally out of left field to have the functions have an
> optional "version" parameter, defaulted to null, that would be used to give
> backwards compatible results if and when we do make a breaking change?

So.. the code that's in pg_dump today works to go from "whatever the
connected server's version is" to "whatever the version is of the
pg_dump command itself". If we had the code in libpgcommon, and
functions in the backend to get at it along with psql having that code,
you could then, using the code we have today, go from a bunch of
'source' versions to 'target' version of either the version of the psql
command, or that of the server.

That is, consider a future where this is all done and all that crazy
version-specific code in pg_dump has been moved to libpgcommon in v14,
and then you have a v15 psql, so:

psql v15 connected to PG v14:

You do: \dct mytable -- psql internal command to get 'create table'
Result: a CREATE TABLE that works for v15

You do: DESCRIBE mytable; -- PG backend function to get 'create table'
Result: a CREATE TABLE that works for v14

Without having to add anything to what we're already doing (yes, yes,
beyond the complications of moving this stuff into libpgcommon, but at
least we're not having to create some kind of matrix of "source PG
version 10, target PG version 12" into PG14).

A bit crazy, sure, but would certainly be pretty useful.

Thanks,

Stephen