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

Cheers,

Frank.
PS: Here some "real world example":

franktest=# create table testtab (id integer, something varchar);
CREATE TABLE
franktest=# create view testview as select * from testtab;
CREATE VIEW
franktest=# comment on view testview is 'some comment';
COMMENT
franktest=# insert into testtab select x, x from generate_series(1,100000) x;
INSERT 0 100000
franktest=# exit

postgres@postgresql-01:~$ pg_dump --schema-only --table=testview franktest
--
-- PostgreSQL database dump
--

-- Dumped from database version 13.4 (Debian 13.4-1.pgdg110+1)
-- Dumped by pg_dump version 13.4 (Debian 13.4-1.pgdg110+1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: testview; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW public.testview AS
SELECT testtab.id,
   testtab.something
  FROM public.testtab;


ALTER TABLE public.testview OWNER TO postgres;

--
-- Name: VIEW testview; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON VIEW public.testview IS 'some comment';


--
-- PostgreSQL database dump complete
--

Hint: The "--table" option accepts a pattern, so, if you're lucky, you can find one matching all your view names, and it can be used repeatedly, so you should be able to produce some automatism which can generate it using "SELECT table_name FROM information_schema.views" or something like that.

P {margin-top:0;margin-bottom:0;}

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.

Thanks!