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