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