Retail DDL

On Thu, 24 Jul 2025 at 16:26, Andrew Dunstan <andrew@dunslane.net> 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.

Could you do anything with the reg* data types?

Have pg_get_ddl (regclass) return a CREATE TABLE or CREATE VIEW command, as
appropriate, while pg_get_ddl (regtype) would return CREATE TYPE etc.

They don't cover all the object types but might be helpful for at least
some cases.

On Thu Jul 24, 2025 at 5:26 PM -03, 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.

What about SHOW CREATE TABLE? Some other databases support this syntax.

--
Matheus Alcantara

On Thu, 24 Jul 2025 at 21:46, Matheus Alcantara
<matheusssilv97@gmail.com> wrote:

On Thu Jul 24, 2025 at 5:26 PM -03, 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.

What about SHOW CREATE TABLE? Some other databases support this syntax.

SHOW is reserved for returning parameters. I don't think we want to
muddy the purpose of that command.

--
Thom Brown
Data Egret (https://dataegret.com)

Andrew Dunstan <andrew@dunslane.net> writes:

.... 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.

I'm good with pg_get_{objecttype}_ddl. The reason I like it is that
that sets expectations for what the function can do, and we don't
have to immediately cover every object type there is in order to not
have a function with unexpected restrictions.

A small advantage is that, for object types having a reg* pseudotype,
we can declare the function as (say)

pg_get_table_ddl(regclass)

and that means this will work with no additional decoration:

select pg_get_table_ddl('mytable');

Nearby, Isaac suggested sort of the reverse of that, where
you'd have to write

select pg_get_ddl('mytable'::regclass);

but I don't see any great advantages in that --- and it can't scale
to object types that lack a reg* type.

regards, tom lane

On Fri, Jul 25, 2025 at 3:06 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Andrew Dunstan <andrew@dunslane.net> writes:

.... 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.

I'm good with pg_get_{objecttype}_ddl. The reason I like it is that
that sets expectations for what the function can do, and we don't
have to immediately cover every object type there is in order to not
have a function with unexpected restrictions.

A small advantage is that, for object types having a reg* pseudotype,
we can declare the function as (say)

pg_get_table_ddl(regclass)

and that means this will work with no additional decoration:

select pg_get_table_ddl('mytable');

Nearby, Isaac suggested sort of the reverse of that, where
you'd have to write

select pg_get_ddl('mytable'::regclass);

but I don't see any great advantages in that --- and it can't scale
to object types that lack a reg* type.

OTOH, we can have a common function and pass object type as parameter
i.e. select pg_get_ddl('table', 'mytable'), with this the same
function can be extended for different object types.

--
Regards,
Dilip Kumar
Google

Dilip Kumar <dilipbalaut@gmail.com> writes:

OTOH, we can have a common function and pass object type as parameter
i.e. select pg_get_ddl('table', 'mytable'), with this the same
function can be extended for different object types.

And you'll work regclass/regtype/etc into that how? AFAICS the
only way would involve fundamentally redundant typing:

select pg_get_ddl('table', 'mytable'::regclass);

How is that better?

regards, tom lane

On Fri, Jul 25, 2025 at 9:23 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Dilip Kumar <dilipbalaut@gmail.com> writes:

OTOH, we can have a common function and pass object type as parameter
i.e. select pg_get_ddl('table', 'mytable'), with this the same
function can be extended for different object types.

And you'll work regclass/regtype/etc into that how? AFAICS the
only way would involve fundamentally redundant typing:

select pg_get_ddl('table', 'mytable'::regclass);

How is that better?

I got your point that now we need redundant typing for the objects
which already have reg* types, I think the advantage of this is we
don't need to have different functions names if we support multiple
object types like pg_get_table_ddl, pg_get_function_ddl,
pg_get_role_ddl, instead we can just do that with pg_get_ddl('table',
'mytable'); pg_get_ddl('function', 'mytable'); pg_get_ddl('role',
'myrole'); etc.

--
Regards,
Dilip Kumar
Google

Hi Andrew,

On Fri, Jul 25, 2025 at 1:56 AM Andrew Dunstan <andrew@dunslane.net> 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.

We have something roughly in that category in the form of functions in
[1]: https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-INFO-OBJECT-TABLE
the given object as well?

I mentioned contracting because the new interface may not handle
objects like columns which do not have an independent DDL. Or maybe we
could re-imagine DDL for such objects as ALTER .... ADD variant for
that object. For example, in case of a column it would be ALTER TABLE
... ADD COLUMN ....

[1]: https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-INFO-OBJECT-TABLE

--
Best Wishes,
Ashutosh Bapat

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?

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).

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.

--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/

I have been thinking of this from a little different direction. We
already have all the needed functionality in pg_dump so why not just
have an option to do

CREATE EXTENSION pg_dump;

Which would wrap and expose whatever the current version of pg_dump is doing.

It still would need to resolve the difficult question of naming
things, but otherways it looks like just a certain amount of
mechanical work.

We could even have just one set of functions with a few possible argument types

pg_dump(object oid, options text);
pg_dump(object text, options text);

---
Hannu

A related improvement would be to also support

CREATE EXTENSION psql;

To make at least the `\d ...` commands available to any client

And while we are at it, why not also

CREATE EXTENSION pgbench;

To make the fancy random distribution functions (at least) from
pgbench available from inside the database.

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

=?utf-8?Q?=C3=81lvaro?= Herrera <alvherre@kurilemu.de> writes:

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?

In the same vein: would we expect this command to also build the
table's indexes? What about foreign key constraints, which might
well reference tables that don't exist yet?

Once you start crawling down this rabbit-hole, you soon realize
why pg_dump is as complicated as it is.

regards, tom lane

Hi,

On Jul 25, 2025 at 21:35 +0800, Tom Lane <tgl@sss.pgh.pa.us>, wrote:

=?utf-8?Q?=C3=81lvaro?= Herrera <alvherre@kurilemu.de> writes:

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?

In the same vein: would we expect this command to also build the
table's indexes? What about foreign key constraints, which might
well reference tables that don't exist yet?

Once you start crawling down this rabbit-hole, you soon realize
why pg_dump is as complicated as it is.

First of all, +1 to this suggestion.
I've long believed there should be a standard way to get a table's DDL (like MySQL and Oracle have), especially when our DBAs encounter issues in customer
environments or when we need to cross-validate problems across different cluster versions.
This would make problem reproduction much more convenient. Currently, we're using pg_dump as our workaround.

Regarding the complexity you mentioned - absolutely, it's a real challenge.
MySQL's approach is to include all of a table's indexes in the DDL output. But this becomes problematic when dealing with foreign key dependencies between tables.

I think we could start with implementing basic table DDL and index generation first, as these are the most commonly needed features in practice.
For other objects related to the table, we can clearly document them.

Additionally, I have another suggestion - could we have a quick backslash command to display DDL? Something like \d+ t1, or perhaps \dddl? Looking at the code,
it seems there aren't many available command slots remaining.

--
Zhang Mingli
HashData

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.

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

Hi!

On Thu, 14 Aug 2025 at 23:30, Andrew Dunstan <andrew@dunslane.net> wrote:

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 ?).

DESCRIBE would be confusing with extended protocol Describe message,
used for prepared statements and portals. At least for me this would
be confusing.

Builtin
functions are also likely to be faster.

We are not actually aiming for speed here, aren’t we?

Overall, Im +1 on `pg_get_{objecttype}_ddl` or maybe
`pg_show_{objecttype}_ddl` design.

--
Best regards,
Kirill Reshke

On Sat, 16 Aug 2025 at 10:08, I wrote:

On Thu, 14 Aug 2025 at 23:30, Andrew Dunstan <andrew@dunslane.net> wrote:

Builtin
functions are also likely to be faster.

We are not actually aiming for speed here, aren’t we?

I want to clarify here: I do not think consuming limiter resources of
catalog OID for builtin functions is worth the benefit here.

Overall, Im +1 on `pg_get_{objecttype}_ddl` or maybe
`pg_show_{objecttype}_ddl` design.

After putting some more thought into it, maybe we can implement the
whole thing as contrib extension? This would be the most Postgres-y
way to me.

--
Best regards,
Kirill Reshke

On 2025-Aug-16, Kirill Reshke wrote:

After putting some more thought into it, maybe we can implement the
whole thing as contrib extension? This would be the most Postgres-y
way to me.

If we do that, then core tools such as psql or pg_dump can never depend
on them. -1 from me.

--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"Always assume the user will do much worse than the stupidest thing
you can imagine." (Julien PUYDT)