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

So, this doesn't solve the issue of finding the object definitions via SQL,
but pg_extractor provides a way to get all the individual object
definitions output into organized folders/files. Each object goes into its
own file and things like tables include all their dependent objects in that
file. I used this extensively for checking database schema into git to
track changes, something nearly impossible with a single dump file since
objects are in an order that can change at any time.

https://github.com/omniti-labs/pg_extractor

It's also not really doing anything special other than taking advantage of
pg_restore options to get a catalog list (-l) then feed that object list
back into itself (-L) to only get the specified object. So if this tool
isn't doing quite what you need, knowing that might help find a solution to
what you're looking for.

On Mon, Mar 20, 2023 at 11:49 AM richard coleman <
rcoleman(dot)ascentgl(at)gmail(dot)com> wrote:

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