Re: SQL query that can be used to generate the same output shown in the SQL tab within pgadmin?

Please see the following Perl module we have developed.  Maybe someone
can incorporate this directly into postgres someday so we can do 'SHOW
CREATE TABLE' and etc from any client and get the DDL.

This won't run out of the box for you as there are other modules we use
internally that are not included.  But you'll find every single query
you'll ever need to generate DDL for tables, views, constraints,
indexes, sequences, aggregates.

It doesn't have everything-everything that postgres can generate... it's
missing some of the newer things like foreign data wrappers and whatnot,
but the bulk of it is there.

On 1/12/22 05:12, Dave Page wrote:
> Hi
>
> On Wed, Jan 12, 2022 at 12:45 AM Ni Ne <nineoften(at)hotmail(dot)com> wrote:
>
> My question is not specifically about how to use pgadmin, I hope
> that's okay.
>
> When are you in pgadmin you can select an object from the tree on
> the left-pane, like a View. While that object is selected, if you
> click the SQL button in the main-pane toolbar, you can see all SQL
> commands that were used to create/modify that object. So for a
> view it will show you the SQL commands used to create that view
> and its SQL code, the comment/description if you added one, any
> grant statements, etc.
>
> My question is, is there an SQL query I can use to generate that
> same output myself? I am writing a script that will backup my view
> definitions and such, and if I could grab the entirety of that
> output that is shown in the SQL tab it would be perfect. I am not
> trying to leverage pgadmin specifically for this. My plan was to
> use psycopg2 to send the query directly to my db server and fetch
> the results, and stash that output into a file.
>
> I found this query that can be used to generate most of that
> output. Running against a view named myview it would be:
>
> SELECT * FROM pg_views WHERE viewname='myview';
>
> Some problems with that output is that it doesn't generate the
> full SQL commands that would be needed to re-create that object in
> its entirety, and doesn't contain the comment.
>
>
> Unfortunately that's the best you'll get from an SQL query. pgAdmin
> has a significant amount of code to reverse engineer the DDL from the
> system catalogs (as does, for example, pg_dump).
>
> --
> Dave Page
> Blog: https://pgsnake.blogspot.com
> Twitter: @pgsnake
>
> EDB: https://www.enterprisedb.com
>