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

[Mark Murawski]

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.

On 1/12/22 05:12, Dave Page wrote:

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.

Hi

On Wed, Jan 12, 2022 at 12:45 AM Ni Ne <nineof...@hotmail.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.




--
Dave Page
Blog: https://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: https://www.enterprisedb.com

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

PostgresInfo.pm
Description: Perl program