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@xxxxxxxxxxxxx> wrote:
richard coleman <rcoleman.ascentgl@xxxxxxxxx> 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
