Thread: PostgreSQL equivalents to dbms_metadata.get_ddl & dbms_metadata.get_dependent_ddl

Use "psql -E" and then use the shortcut commands to get DDL stuff.  You will see the SQL used behind the scenes to get the stuff.

\d myschema.mytable
\df myschema.function
etc.

Regards,
Michael Vitale


richard coleman wrote on 3/19/2023 5:36 PM:

Hello all, 

I am trying to find a simple way to get access the DDL information for PostgreSQL tables, constraints, and indices via SQL.

In Oracle there are the dbms_metadata.get_ddl() and dbms_metadata.get_dependent_ddl() functions that you can call on dual to get this information.

EX:

-- table DDL:

select dbms_metadata.get_ddl('TABLE', 'MY_TABLE','MY_SCHEMA') as "table_ddl" from dual;

-- indices DDL
select dbms_metadata.get_dependent_ddl('INDEX','MY_TABLE','MY_SCHEMA') as "table_indicies" from dual;

-- constraints DDL
select dbms_metadata.get_dependent_ddl('CONSTRAINT','MY_TABLE','MY_SCHEMA') as "constraints_ddl" from dual;

Is there an equivalent function to do the same in PostgreSQL?

I know there are ways via pg_dump and psql, but what I need is a way to do so via SQL.

Thanks, 

rik.

Regards,

Michael Vitale

Michaeldba@sqlexec.com

703-600-9343 

On Mar 19, 2023, at 10:49 PM, richard coleman <rcoleman.ascentgl@gmail.com> wrote:

I wonder how difficult it would have been to create SQL functions out of what is basically psql or pg_dump magic.

If you ignore the answers on StackOverflow that say to use pg_dump and "psql -E", there must be close to 10 other answers with various SQL functions which do exactly that here:

How to generate the "create table" sql statement for an existing table in postgreSQL stackoverflow.com

I recommend you read through them and test them to find the best version. I hope one of them fits your needs. If so, I hope you report back here with which one you went with. :-)

Hoping this helps,

Ed

Am 19.03.23 um 22:36 schrieb richard coleman:
> Hello all,
> I am trying to find a simple way to get access the DDL information for
> PostgreSQL tables, constraints, and indices via SQL.
>
> In Oracle there are the dbms_metadata.get_ddl()
> and dbms_metadata.get_dependent_ddl() functions that you can call on
> dual to get this information.
>
> EX:
> -- table DDL:
> select dbms_metadata.get_ddl('TABLE', 'MY_TABLE','MY_SCHEMA') as
> "table_ddl" from dual;
>
> -- indices DDL
> select dbms_metadata.get_dependent_ddl('INDEX','MY_TABLE','MY_SCHEMA')
> as "table_indicies" from dual;
>
> -- constraints DDL
> select
> dbms_metadata.get_dependent_ddl('CONSTRAINT','MY_TABLE','MY_SCHEMA')
> as "constraints_ddl" from dual;
>
> Is there an equivalent function to do the same in PostgreSQL?
>
> I know there are ways via pg_dump and psql, but what I need is a way
> to do so via SQL.
>
> Thanks,
> rik.

Every sane RDBMS has the INFORMATION_SCHEMA according to the ISO SQL
Standard, so that you can find out all of these.

Btw, the opposite isn't true. The existence of the INFORMATION_SCHEMA
alone doesn't make an RDBMS sane.

Of course there are proprietary views in pg_catalog as well. And pg_dump
-s can dump the whole schema in clear text.

Regards,

Holger


--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012

Well, there are system funcs and such too, check out https://www.postgresql.org/docs/15/functions-info.html.
See Table 9.71. System Catalog Information Functions

pg_get_constraintdef()
pg_get_functiondef()
pg_get_indexdef()
pg_get_triggerdef()
pg_get_viewdef()
...
...

Regards,
Michael Vitale



richard coleman wrote on 3/20/2023 9:59 AM:

Ed, 

Actually that's where I started.  Most of the answers on StackOverflow either don't work on the latest versions of PostgreSQL or didn't include the complete DDL.  Most were missing indices and constraints.  As a previous poster remarked, psql -E and then copy/pasting and using that as the basis for custom function(s) seems to be my only path forward.

It's a shame that users are basically forced to write not entirely simplistic SQL themselves.  The SQL appears to already exist in PostgreSQL as evidenced by the results of psql -E, it just doesn't seem that it was packaged into user accessible SQL functions.  I've noticed that the answer to many questions seem to be "use this psql \ command".  Is there any reason that all of the special psql magic functions couldn't be exposed as default SQL functions?

Oh, well.  Thanks for all the help.

rik.

On Mon, Mar 20, 2023 at 2:33 AM Edward J. Sabol <edwardjsabol@gmail.com> wrote:

On Mar 19, 2023, at 10:49 PM, richard coleman <rcoleman.ascentgl@gmail.com> wrote:

I wonder how difficult it would have been to create SQL functions out of what is basically psql or pg_dump magic.

If you ignore the answers on StackOverflow that say to use pg_dump and "psql -E", there must be close to 10 other answers with various SQL functions which do exactly that here:

How to generate the "create table" sql statement for an existing table in postgreSQL stackoverflow.com

I recommend you read through them and test them to find the best version. I hope one of them fits your needs. If so, I hope you report back here with which one you went with. :-)

Hoping this helps,

Ed

Regards,

Michael Vitale

Michaeldba@sqlexec.com

703-600-9343 

richard coleman <rcoleman.ascentgl@gmail.com> writes:
> Look below for the difference in the two functions, how they are used, the
> amount of knowledge you need to posses in order to run them and the
> difference in the output.

I'm not finding this terribly convincing, because I don't exactly see
what is the use-case for having that DDL in isolation.  Or in other
words, what is your use-case that you find pg_dump so unsatisfactory
for?  It knows all this stuff, and it also knows things like which
other objects a given object depends on and how to cope with circular
dependencies.  Most of the reasons that I can think of for wanting
the pg_get_*def functions that we don't already have seem to reduce
to building partially-baked versions of pg_dump.

If your concern is that you can't get pg_dump to slice up the schema
in exactly the way you want, adding some more options to it might be
an easier sell than implementing/maintaining a bunch of duplicate
functionality.  We just got done adding [1], for example.

                        regards, tom lane

[1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=a563c24c9574b74f4883c004c89275bba03c3c26

richard coleman schrieb am 20.03.2023 um 16:48:
> My use case is copying and maintaining the same tables in Oracle and
> PostgreSQL as well as detecting and applying DDL changes between the
> two systems.
We are using Liquibase as a schema migration tool which allows us to
specify a single "changelog" (using their XML format) that can be
applied to Oracle and Postgres alike.

So we never need to "compare" definitions, we just run the changelog
script to update the database schema.

Some differences in e.g. available data types can be dealt with using
Liquibase's properties or if everything else fails using different
changesets for Postgres and Oracle (which is typically only needed
for views and procedural code).


Of course it might not be feasible to re-structure your existing
infrastructure for schema migrations to use a completely different
approach.

Thomas

Thomas, 

Thanks for letting me know about this product.  Looking at their page doesn't fill me with a sense of comfort.  There's too little information, nothing clear about on prem. vs in cloud, no pricing information, etc.  Just a lot of marketing fluff and directions to contact their sales people.

Of course, this doesn't address the fundamental concern which is that currently PostgreSQL makes it needlessly difficult to get basic database information, in this case DDL via SQL.  Liquibase, like pg_dump, is yet another utility needed to fill in for this gap in PostgreSQL native functionality.

I've resigned myself to having assemble the information I need by hand.  I can only hope that the PostgreSQL developers see their way to including it in future versions.

Thanks again, 

rik.

On Mon, Mar 20, 2023 at 12:10 PM Thomas Kellerer <shammat@gmx.net> wrote:

richard coleman schrieb am 20.03.2023 um 16:48:
> My use case is copying and maintaining the same tables in Oracle and
> PostgreSQL as well as detecting and applying DDL changes between the
> two systems.
We are using Liquibase as a schema migration tool which allows us to
specify a single "changelog" (using their XML format) that can be
applied to Oracle and Postgres alike.

So we never need to "compare" definitions, we just run the changelog
script to update the database schema.

Some differences in e.g. available data types can be dealt with using
Liquibase's properties or if everything else fails using different
changesets for Postgres and Oracle (which is typically only needed
for views and procedural code).


Of course it might not be feasible to re-structure your existing
infrastructure for schema migrations to use a completely different
approach.

Thomas

Tom, 

My use case is copying and maintaining the same tables in Oracle and PostgreSQL as well as detecting and applying DDL changes between the two systems.

I can get the Oracle side simply enough by making use of the dbms_metadata.get_ddl and dbms_metadata.get_dependent_ddl functions. I am trying to do the same without having to ssh into a server, then run pg_dump, and then rummage around in the results, extract what I need, copy it back to my workstation and compare it to the Oracle DDL.  You can get that data from PostgreSQL in SQL, but you have to write something on the order of what Michael's done in order to do so.

So my concern is that I can't get this information simply from SQL.  I know it exists, I can see much of it when I run psql with the -E flag.  I know that it exists in pg_dump.  I know that pieces of it are scattered across various system tables in the database itself.  I feel that something this basic should be exposed via standard default functions.  Unfortunately, it seems that a lot of functionality is locked behind psql magic commands, or in ancillary tools like pg_dump.

I hope that helps clear things up somewhat.

rik.

On Mon, Mar 20, 2023 at 11:10 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

richard coleman <rcoleman.ascentgl@gmail.com> writes:
> Look below for the difference in the two functions, how they are used, the
> amount of knowledge you need to posses in order to run them and the
> difference in the output.

I'm not finding this terribly convincing, because I don't exactly see
what is the use-case for having that DDL in isolation.  Or in other
words, what is your use-case that you find pg_dump so unsatisfactory
for?  It knows all this stuff, and it also knows things like which
other objects a given object depends on and how to cope with circular
dependencies.  Most of the reasons that I can think of for wanting
the pg_get_*def functions that we don't already have seem to reduce
to building partially-baked versions of pg_dump.

If your concern is that you can't get pg_dump to slice up the schema
in exactly the way you want, adding some more options to it might be
an easier sell than implementing/maintaining a bunch of duplicate
functionality.  We just got done adding [1], for example.

                        regards, tom lane

[1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=a563c24c9574b74f4883c004c89275bba03c3c26