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

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