Thread

1. Retail DDL

   Andrew Dunstan <andrew@dunslane.net> — 2025-07-24T20:26:04Z

   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.
   
   
   cheers
   
   
   andrew
   
   
   --
   Andrew Dunstan
   EDB: https://www.enterprisedb.com
   
   
   
   
   

2. Re: Retail DDL

   Isaac Morland <isaac.morland@gmail.com> — 2025-07-24T20:29:36Z

   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.
   

3. Re: Retail DDL

   Matheus Alcantara <matheusssilv97@gmail.com> — 2025-07-24T20:46:09Z

   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
   
   
   
   

4. Re: Retail DDL

   Thom Brown <thom@linux.com> — 2025-07-24T20:58:00Z

   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)
   
   
   
   

5. Re: Retail DDL

   Tom Lane <tgl@sss.pgh.pa.us> — 2025-07-24T21:36:10Z

   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
   
   
   
   

6. Re: Retail DDL

   Dilip Kumar <dilipbalaut@gmail.com> — 2025-07-25T03:48:15Z

   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
   
   
   
   

7. Re: Retail DDL

   Tom Lane <tgl@sss.pgh.pa.us> — 2025-07-25T03:53:31Z

   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
   
   
   
   

8. Re: Retail DDL

   Dilip Kumar <dilipbalaut@gmail.com> — 2025-07-25T04:06:27Z

   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
   
   
   
   

9. Re: Retail DDL

   Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> — 2025-07-25T04:34:16Z

   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]. How about extending/contracting that interface to give us DDL of
   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
   
   
   
   

10. Re: Retail DDL

    Álvaro Herrera <alvherre@kurilemu.de> — 2025-07-25T08:34:57Z

    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/
    
    
    
    

11. Re: Retail DDL

    Hannu Krosing <hannuk@google.com> — 2025-07-25T08:43:50Z

    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
    
    
    
    
    
    
    
    
    
    On Fri, Jul 25, 2025 at 10:35 AM Álvaro Herrera <alvherre@kurilemu.de> 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?
    >
    > 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/
    >
    >
    
    
    
    

12. Re: Retail DDL

    Hannu Krosing <hannuk@google.com> — 2025-07-25T08:48:46Z

    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 Fri, Jul 25, 2025 at 10:43 AM Hannu Krosing <hannuk@google.com> wrote:
    >
    > 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
    >
    >
    >
    >
    >
    >
    >
    >
    >
    > On Fri, Jul 25, 2025 at 10:35 AM Álvaro Herrera <alvherre@kurilemu.de> 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?
    > >
    > > 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/
    > >
    > >
    
    
    
    

13. Re: Retail DDL

    Andrew Dunstan <andrew@dunslane.net> — 2025-07-25T12:55:02Z

    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
    
    
    
    
    

14. Re: Retail DDL

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-07-25T13:35:11Z

    =?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
    
    
    
    

15. Re: Retail DDL

    Zhang Mingli <zmlpostgres@gmail.com> — 2025-07-25T14:04:38Z

    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
    

16. Re: Retail DDL

    Ziga <ziga@ljudmila.org> — 2025-08-14T02:29:28Z

    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.
    
    
    
    
    
    

17. Re: Retail DDL

    Andrew Dunstan <andrew@dunslane.net> — 2025-08-14T18:29:53Z

    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
    
    
    
    
    

18. Re: Retail DDL

    Kirill Reshke <reshkekirill@gmail.com> — 2025-08-16T05:08:28Z

    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
    
    
    
    

19. Re: Retail DDL

    Kirill Reshke <reshkekirill@gmail.com> — 2025-08-16T09:43:20Z

    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
    
    
    
    

20. Re: Retail DDL

    Álvaro Herrera <alvherre@kurilemu.de> — 2025-08-16T12:52:30Z

    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)
    
    
    
    

21. Re: Retail DDL

    Álvaro Herrera <alvherre@kurilemu.de> — 2025-08-16T12:59:42Z

    Hello Ziga,
    
    On 2025-Aug-14, Ziga wrote:
    
    > 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.
    
    It's never been the case, actually --- with older versions, you only
    needed to let the OID counter wrap around, and then it would be possible
    to create (say) a function with the same OID as a table.  Back then it
    was unusual that OIDs would wrap around, and even then it's hard to be
    so unlucky that the OID generator gives you the same value exactly when
    it's time to create an object of a different type; but it's certainly
    always been a possibility.  So the idea of passing just an OID is
    fundamentally bogus.  The class-id (or some other way to identify which
    type of object the user wants) must be mandatory, in order for the API
    to be robust.
    
    Regards
    
    -- 
    Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
    "I dream about dreams about dreams", sang the nightingale
    under the pale moon (Sandman)
    
    
    
    

22. Re: Retail DDL

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-08-16T14:22:50Z

    =?utf-8?Q?=C3=81lvaro?= Herrera <alvherre@kurilemu.de> writes:
    > 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.
    
    pg_dump will never depend on any such thing anyway.  It has too many
    special-purpose requirements, like needing to split up object
    definitions in particular ways, cope with very old server versions,
    etc etc.  Insisting that this feature support pg_dump is a good way
    of making sure that nothing useful will emerge at all.
    
    Maybe we could replace (some of) psql's describe.c logic with
    server-side code, but I'm skeptical that there'd be much win
    there either.
    
    So I don't really buy Álvaro's argument above.  It'd be better
    to design to some concrete requirement that isn't either of
    those.  Unfortunately, it's not clear to me that anyone has
    a concrete use-case in mind that isn't either of those.
    
    			regards, tom lane
    
    
    
    

23. Re: Retail DDL

    Jelte Fennema-Nio <postgres@jeltef.nl> — 2025-08-18T08:57:40Z

    On Sat, 16 Aug 2025 at 16:23, Tom Lane <tgl@sss.pgh.pa.us> wrote:
    > So I don't really buy Álvaro's argument above.  It'd be better
    > to design to some concrete requirement that isn't either of
    > those.  Unfortunately, it's not clear to me that anyone has
    > a concrete use-case in mind that isn't either of those.
    
    I have wanted this MANY times. I've had this as a PG user: I think
    it's literally the first thing I did when connecting to a Postgres
    server the first time. Coming from MySQL, I wanted to see the exact
    table definition, and there it was as easy as "DESCRIBE some_table". I
    quickly learned about "\d some_table", but it was not the same. I
    could not copy paste the result and slightly modify it to create a new
    (but similar) table, which is something I still would like to have to
    this day. I do not know table DDL by heart, so often I just want an
    example to start from.
    
    I don't think this should be thought of as replacing \d, but it could
    greatly improve it. Just like "\d+ some_view" shows the view
    definition, "\d+ some_table" could be showing the table definition in
    SQL. Not having this in core, will make it impossible for psql to show
    such definitions.
    
    Finally I've also wanted this as an extension author: We basically
    built something like this for Citus to be able to recreate
    shard-tables with the same things as parent tables. And I did a
    similar thing in pg_duckdb again. I don't think I would have been able
    to use this code verbatim for these usecases (similarly to how pg_dump
    couldn't), but I would at least have a good base that I could copy
    paste from.
    
    
    
    

24. Re: Retail DDL

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-08-18T13:57:21Z

    Jelte Fennema-Nio <postgres@jeltef.nl> writes:
    > On Sat, 16 Aug 2025 at 16:23, Tom Lane <tgl@sss.pgh.pa.us> wrote:
    >> So I don't really buy Álvaro's argument above.  It'd be better
    >> to design to some concrete requirement that isn't either of
    >> those.  Unfortunately, it's not clear to me that anyone has
    >> a concrete use-case in mind that isn't either of those.
    
    > I have wanted this MANY times. I've had this as a PG user: I think
    > it's literally the first thing I did when connecting to a Postgres
    > server the first time. Coming from MySQL, I wanted to see the exact
    > table definition, and there it was as easy as "DESCRIBE some_table".
    
    You haven't actually defined what "this" is.  For starters, do you
    really want this output to be included in \d?  Seems like one part
    or the other of such output would be clutter, so I'd be more minded
    to leave \d alone and invent some new command.  (By analogy to \sf,
    maybe \st and so on?)
    
    But the real issue is what to print.  In the case of a table, should
    we also show its indexes?  What about foreign keys to or from other
    tables?  If it's a partitioned table, what about the partitions?
    I'm not sure this is as simple as it seems.
    
    			regards, tom lane
    
    
    
    

25. Re: Retail DDL

    Jelte Fennema-Nio <postgres@jeltef.nl> — 2025-08-18T14:23:52Z

    On Mon, 18 Aug 2025 at 15:57, Tom Lane <tgl@sss.pgh.pa.us> wrote:
    > You haven't actually defined what "this" is.  For starters, do you
    > really want this output to be included in \d?  Seems like one part
    > or the other of such output would be clutter, so I'd be more minded
    > to leave \d alone and invent some new command.  (By analogy to \sf,
    > maybe \st and so on?)
    
    That makes sense, I don't think I had seen the \s ones before. IMO it
    would be super useful to have a \s for every type of object that
    currently has a \d (but those don't have to be all added in the same
    patchset ofcours, starting with tables seems totally sensible)
    
    > But the real issue is what to print.
    
    I think you're making this sound much harder than it actually is. I
    think it would be perfect if it had exactly the same info as \d but in
    SQL form instead of some "easy to understand by humans form".  So:
    
    > should we also show its indexes?
    
    Yes
    
    > What about foreign keys to or from other
    > tables?
    
    Yes, both from and to
    
    > If it's a partitioned table, what about the partitions?
    
    Definitely not by default, way too much clutter.
    
    I think having a DESCRIBE keyword is probably not what we want, but
    adding a pg_get_tabledef function seems totally reasonable. I even
    proposed that at some point[1], but apparently never followed up with
    Kirk (cc-ed now). It could even have options for all the questions
    that you're asking like, so we'd "just" need to decide on the
    defaults:
    
    SELECT pg_get_tabledef('my_table', include_indexes => true,
    include_partitions => true)
    
    [1]: https://www.postgresql.org/message-id/CAGECzQRuHBs9gjPbvgabQv8XS3QRU9Ex=nH84S_1=wo4POzBzg@mail.gmail.com
    
    
    
    

26. Re: Retail DDL

    Andrew Dunstan <andrew@dunslane.net> — 2025-08-18T14:32:38Z

    On 2025-08-18 Mo 9:57 AM, Tom Lane wrote:
    > Jelte Fennema-Nio <postgres@jeltef.nl> writes:
    >> On Sat, 16 Aug 2025 at 16:23, Tom Lane <tgl@sss.pgh.pa.us> wrote:
    >>> So I don't really buy Álvaro's argument above.  It'd be better
    >>> to design to some concrete requirement that isn't either of
    >>> those.  Unfortunately, it's not clear to me that anyone has
    >>> a concrete use-case in mind that isn't either of those.
    >> I have wanted this MANY times. I've had this as a PG user: I think
    >> it's literally the first thing I did when connecting to a Postgres
    >> server the first time. Coming from MySQL, I wanted to see the exact
    >> table definition, and there it was as easy as "DESCRIBE some_table".
    > You haven't actually defined what "this" is.  For starters, do you
    > really want this output to be included in \d?  Seems like one part
    > or the other of such output would be clutter, so I'd be more minded
    > to leave \d alone and invent some new command.  (By analogy to \sf,
    > maybe \st and so on?)
    
    
    Yes, I agree. A separate metacommand would make more sense. Maybe 
    something like \sdx where x is some object type designator. (sd stands 
    for show ddd or show definition)
    
    
    >
    > But the real issue is what to print.  In the case of a table, should
    > we also show its indexes?  What about foreign keys to or from other
    > tables?  If it's a partitioned table, what about the partitions?
    > I'm not sure this is as simple as it seems.
    >
    > 			
    
    
    Agreed it's not simple, but that doesn't mean we should not do it. 
    Tables are the most obviously complex case. I'm inclined to say foreign 
    keys to but not from, and also include indexes. But maybe we can provide 
    several flavors, by allowing some function options, e.g.
    
        \sdt would show the basic table def without FKs or secondary 
    indexes, and
    
        \sdt+ would show everything
    
    Or we could get more fine-grained.
    
    
    cheers
    
    
    andrew
    
    --
    Andrew Dunstan
    EDB: https://www.enterprisedb.com
    
    
    
    
    

27. Re: Retail DDL

    Isaac Morland <isaac.morland@gmail.com> — 2025-08-18T14:39:37Z

    On Mon, 18 Aug 2025 at 10:32, Andrew Dunstan <andrew@dunslane.net> wrote:
    
    
    > > But the real issue is what to print.  In the case of a table, should
    > > we also show its indexes?  What about foreign keys to or from other
    > > tables?  If it's a partitioned table, what about the partitions?
    > > I'm not sure this is as simple as it seems.
    >
    > Agreed it's not simple, but that doesn't mean we should not do it.
    > Tables are the most obviously complex case. I'm inclined to say foreign
    > keys to but not from, and also include indexes. But maybe we can provide
    > several flavors, by allowing some function options, e.g.
    >
    
    Are you sure you don't mean from but not to?
    
    If I want foreign keys from a table when looking at that table's
    definition, they can be part of a single CREATE TABLE statement. If I want
    foreign keys to that table, I need a bunch of ALTER TABLE statements naming
    the other tables whose foreign keys point at the table in question.
    

28. Re: Retail DDL

    Andrew Dunstan <andrew@dunslane.net> — 2025-08-18T14:57:35Z

    On 2025-08-18 Mo 10:39 AM, Isaac Morland wrote:
    > On Mon, 18 Aug 2025 at 10:32, Andrew Dunstan <andrew@dunslane.net> wrote:
    >
    >     > But the real issue is what to print.  In the case of a table, should
    >     > we also show its indexes?  What about foreign keys to or from other
    >     > tables?  If it's a partitioned table, what about the partitions?
    >     > I'm not sure this is as simple as it seems.
    >
    >     Agreed it's not simple, but that doesn't mean we should not do it.
    >     Tables are the most obviously complex case. I'm inclined to say
    >     foreign
    >     keys to but not from, and also include indexes. But maybe we can
    >     provide
    >     several flavors, by allowing some function options, e.g.
    >
    >
    > Are you sure you don't mean from but not to?
    >
    > If I want foreign keys from a table when looking at that table's 
    > definition, they can be part of a single CREATE TABLE statement. If I 
    > want foreign keys to that table, I need a bunch of ALTER TABLE 
    > statements naming the other tables whose foreign keys point at the 
    > table in question.
    
    
    Sorry. I mean FK constraints on the table in question. I guess that's 
    "from but not to", yes.
    
    
    cheers
    
    
    andrew
    
    
    --
    Andrew Dunstan
    EDB:https://www.enterprisedb.com
    

29. Re: Retail DDL

    Hannu Krosing <hannuk@google.com> — 2025-08-19T07:27:43Z

    On Sat, Aug 16, 2025 at 4:23 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
    >
    > =?utf-8?Q?=C3=81lvaro?= Herrera <alvherre@kurilemu.de> writes:
    > > 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.
    >
    > pg_dump will never depend on any such thing anyway.  It has too many
    > special-purpose requirements, like needing to split up object
    > definitions in particular ways,
    
    That can be handy for the use of get_ddl_function as well.
    
    I remember creating a view, then creating a set-returning function
    returning that view and then redefining the view to be a select from
    that function.
    
    pg_dump did split this up in a nicel dumpable way, though a little
    different than the original set of DDL.
    
    We definitely want our server functions to be able to also cope with
    circularities.
    
    > cope with very old server versions,
    
    This is never needed here, as we only show DDL for our own version.
    
    > etc etc.  Insisting that this feature support pg_dump is a good way
    > of making sure that nothing useful will emerge at all.
    >
    > Maybe we could replace (some of) psql's describe.c logic with
    > server-side code, but I'm skeptical that there'd be much win
    > there either.
    
    But we already have some trickier parts in the server, like getting
    view and function definitions, foreign key definitions, possibly more.
    
    > So I don't really buy Álvaro's argument above.  It'd be better
    > to design to some concrete requirement that isn't either of
    > those.  Unfortunately, it's not clear to me that anyone has
    > a concrete use-case in mind that isn't either of those.
    
    IMHO pg_dump is actually a good model for "concrete requirements"  as
    any requirements I can think of - and have needed in th epast - are
    some subset of pg_dump --schema-only
    
    
    
    

30. Re: Retail DDL

    Laurenz Albe <laurenz.albe@cybertec.at> — 2025-08-19T08:19:55Z

    On Mon, 2025-08-18 at 09:57 -0400, Tom Lane wrote:
    > Jelte Fennema-Nio <postgres@jeltef.nl> writes:
    > 
    > > I have wanted this MANY times. I've had this as a PG user: I think
    > > it's literally the first thing I did when connecting to a Postgres
    > > server the first time. Coming from MySQL, I wanted to see the exact
    > > table definition, and there it was as easy as "DESCRIBE some_table".
    > 
    > You haven't actually defined what "this" is.  For starters, do you
    > really want this output to be included in \d?  Seems like one part
    > or the other of such output would be clutter, so I'd be more minded
    > to leave \d alone and invent some new command.  (By analogy to \sf,
    > maybe \st and so on?)
    > 
    > But the real issue is what to print.  In the case of a table, should
    > we also show its indexes?  What about foreign keys to or from other
    > tables?  If it's a partitioned table, what about the partitions?
    > I'm not sure this is as simple as it seems.
    
    Right, that is the hard problem.
    We have had this discussion before, e.g. in
    https://www.postgresql.org/message-id/flat/CAFEN2wxsDSSuOvrU03CE33ZphVLqtyh9viPp6huODCDx2UQkYA%40mail.gmail.com
    
    Yours,
    Laurenz Albe
    
    
    
    

31. Re: Retail DDL

    Kirk Wolak <wolakk@gmail.com> — 2025-08-20T19:09:28Z

    On Mon, Aug 18, 2025 at 10:24 AM Jelte Fennema-Nio <postgres@jeltef.nl>
    wrote:
    
    > On Mon, 18 Aug 2025 at 15:57, Tom Lane <tgl@sss.pgh.pa.us> wrote:
    > > You haven't actually defined what "this" is.  For starters, do you
    > > really want this output to be included in \d?  Seems like one part
    > > or the other of such output would be clutter, so I'd be more minded
    > > to leave \d alone and invent some new command.  (By analogy to \sf,
    > > maybe \st and so on?)
    >
    > That makes sense, I don't think I had seen the \s ones before. IMO it
    > would be super useful to have a \s for every type of object that
    > currently has a \d (but those don't have to be all added in the same
    > patchset ofcours, starting with tables seems totally sensible)
    >
    > > But the real issue is what to print.
    >
    > I think you're making this sound much harder than it actually is. I
    > think it would be perfect if it had exactly the same info as \d but in
    > SQL form instead of some "easy to understand by humans form".  So:
    >
    > > should we also show its indexes?
    >
    > Yes
    >
    > > What about foreign keys to or from other
    > > tables?
    >
    > Yes, both from and to
    >
    > > If it's a partitioned table, what about the partitions?
    >
    > Definitely not by default, way too much clutter.
    >
    > I think having a DESCRIBE keyword is probably not what we want, but
    > adding a pg_get_tabledef function seems totally reasonable. I even
    > proposed that at some point[1], but apparently never followed up with
    > Kirk (cc-ed now). It could even have options for all the questions
    > that you're asking like, so we'd "just" need to decide on the
    > defaults:
    >
    > SELECT pg_get_tabledef('my_table', include_indexes => true,
    > include_partitions => true)
    >
    > [1]:
    > https://www.postgresql.org/message-id/CAGECzQRuHBs9gjPbvgabQv8XS3QRU9Ex=nH84S_1=wo4POzBzg@mail.gmail.com
    
    
    Jelte, you helped me start on something with this.  Your code was
    effectively based on "opening" the reference and loading the type
    structures.
    
    TBH, I got bogged down on these kinds of issues.  First if we do it as
    "\st" type commands, it is limited to psql.
    If we do it elsewhere, it becomes server side code, and incredibly version
    dependent.
    And I could not quite get the buy-in (as in this discussion) as to where
    this belongs, and how to codify the limitations.
    
    FWIW, I wrote a BASH script that generates the file using pg_dump as my
    workaround.
    I was a bit resigned to thinking this is probably best as a SQL based
    extension that generates the code.
    
    The stickiness was.  Can we justify creating and supporting this code in a
    pg_gettabledef() [Where I believe it belongs]
    of none of the clients are using this code.  Of course, getting psql to use
    this code would be trivial for a "\s" once it exists.
    but it started feeling like a pretty heavy "sell" to the community.
    
    As I've grown with the community, I understand why it has been missing for
    so long.  What we want from it as a casual
    user wanting to quickly see/copy a complete create table command...  Is
    almost orthogonal to what the clients (psql, pg_dump)
    are looking for, and why they built them into the client.
    
    I think it needs more "buy-in" and a better "definition" of what it is.
    Even if we end up with:
    pg_get_tabledef()  -- User Friendly
    pg_get_tabledef_ex()  -- Proper for Partitions, Foreign Data Tables, etc.
    
    Thoughts?
    
    Kirk