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

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> richard coleman <rcoleman(dot)ascentgl(at)gmail(dot)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
>