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

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(at)gmail(dot)com <mailto:edwardjsabol(at)gmail(dot)com>> wrote:
>
> On Mar 19, 2023, at 10:49 PM, richard coleman
> <rcoleman(dot)ascentgl(at)gmail(dot)com <mailto:rcoleman(dot)ascentgl(at)gmail(dot)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:
>
> apple-touch-icon(at)2(dot)png
> How to generate the "create table" sql statement for an existing
> table in postgreSQL
> <https://stackoverflow.com/questions/2593803/how-to-generate-the-create-table-sql-statement-for-an-existing-table-in-postgr>
> stackoverflow.com
> <https://stackoverflow.com/questions/2593803/how-to-generate-the-create-table-sql-statement-for-an-existing-table-in-postgr>
>
>
> 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(at)sqlexec(dot)com <mailto:michaelvitale(at)sqlexec(dot)com>

703-600-9343