From: MichaelDBA <MichaelDBA@sqlexec.com>
To: richard coleman <rcoleman.ascentgl@gmail.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: PostgreSQL equivalents to dbms_metadata.get_ddl & dbms_metadata.get_dependent_ddl
Date: Mon, 20 Mar 2023 10:06:07 -0400
Message-ID: <ac9c17a9-016a-e84b-1ef3-14ce4380f05d@sqlexec.com> (raw)
In-Reply-To: <CAGA3vBsD_xiwuXWua6bceGToc9ZX1fmOfiaDXkw29zfqLhQ=Uw@mail.gmail.com>
References: <CAGA3vBtNLpGdPG=4gdzvD9zoTSz+52JUkfYCsNSi+mv5Yr+GVw@mail.gmail.com>
<A6F7CC72-F6AC-49B6-8DEC-19787D18D7F5@gmail.com>
<CAGA3vBsD_xiwuXWua6bceGToc9ZX1fmOfiaDXkw29zfqLhQ=Uw@mail.gmail.com>
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 <mailto:edwardjsabol@gmail.com>> wrote:
>
> On Mar 19, 2023, at 10:49 PM, richard coleman
> <rcoleman.ascentgl@gmail.com <mailto: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:
>
> apple-touch-icon@2.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-ex...;
> stackoverflow.com
> <https://stackoverflow.com/questions/2593803/how-to-generate-the-create-table-sql-statement-for-an-ex...;
>
>
> 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 <mailto:michaelvitale@sqlexec.com>
703-600-9343
Attachments:
[image/png] image.png (5.2K, ../ac9c17a9-016a-e84b-1ef3-14ce4380f05d@sqlexec.com/3-image.png)
download | view image
[image/jpeg] pgadvanced3.jpg (20.6K, ../ac9c17a9-016a-e84b-1ef3-14ce4380f05d@sqlexec.com/4-pgadvanced3.jpg)
download | view image
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: MichaelDBA@sqlexec.com, rcoleman.ascentgl@gmail.com
Subject: Re: PostgreSQL equivalents to dbms_metadata.get_ddl & dbms_metadata.get_dependent_ddl
In-Reply-To: <ac9c17a9-016a-e84b-1ef3-14ce4380f05d@sqlexec.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox