public inbox for [email protected]help / color / mirror / Atom feed
Sqlectron 4+ messages / 2 participants [nested] [flat]
* Sqlectron @ 2018-01-22 19:57 Brice Maron <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Brice Maron @ 2018-01-22 19:57 UTC (permalink / raw) To: [email protected] Hi All, I'm new on this ML and I'm currently working on the multi-database client written using React and electron that you can see here : https://sqlectron.github.io/. especially on the postgresql part. I wanted to improve support on DDL extraction of the tables (like create table with all comments, PK, FK, ....) ; but it seems that there is no easy way to get a textual representation of this in sql, am i correct ? Thanks and see you guys soon :) Regards Brice ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Sqlectron @ 2018-01-22 20:52 Joe Conway <[email protected]> parent: Brice Maron <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Joe Conway @ 2018-01-22 20:52 UTC (permalink / raw) To: Brice Maron <[email protected]>; [email protected] On 01/22/2018 02:57 PM, Brice Maron wrote: > I'm new on this ML and I'm currently working on the multi-database > client written using React and electron that you can see here : > https://sqlectron.github.io/. > especially on the postgresql part. > > I wanted to improve support on DDL extraction of the tables (like create > table with all comments, PK, FK, ....) ; but it seems that there is no > easy way to get a textual representation of this in sql, am i correct ? If you start the command line psql client with the -E option it will show you the internal queries used to gather and show information with the "slash" commands, e.g. \d <tablename> Here is what it looks like: 8<-------------------------- psql -E test psql (10.1) Type "help" for help. test=# \d foo ********* QUERY ********** SELECT c.oid, n.nspname, c.relname FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relname ~ '^(foo)$' AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 2, 3; ************************** ********* QUERY ********** SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, c.relhasoids, '', c.reltablespace, CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relpersistence, c.relreplident FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid) WHERE c.oid = '16410'; ************************** ********* QUERY ********** SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod), (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef), a.attnotnull, a.attnum, (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation, a.attidentity, NULL AS indexdef, NULL AS attfdwoptions FROM pg_catalog.pg_attribute a WHERE a.attrelid = '16410' AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum; ************************** ********* QUERY ********** SELECT inhparent::pg_catalog.regclass, pg_catalog.pg_get_expr(c.relpartbound, inhrelid) FROM pg_catalog.pg_class c JOIN pg_catalog.pg_inherits i ON c.oid = inhrelid WHERE c.oid = '16410' AND c.relispartition; ************************** ********* QUERY ********** SELECT pol.polname, pol.polpermissive, CASE WHEN pol.polroles = '{0}' THEN NULL ELSE pg_catalog.array_to_string(array(select rolname from pg_catalog.pg_roles where oid = any (pol.polroles) order by 1),',') END, pg_catalog.pg_get_expr(pol.polqual, pol.polrelid), pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid), CASE pol.polcmd WHEN 'r' THEN 'SELECT' WHEN 'a' THEN 'INSERT' WHEN 'w' THEN 'UPDATE' WHEN 'd' THEN 'DELETE' END AS cmd FROM pg_catalog.pg_policy pol WHERE pol.polrelid = '16410' ORDER BY 1; ************************** ********* QUERY ********** SELECT oid, stxrelid::pg_catalog.regclass, stxnamespace::pg_catalog.regnamespace AS nsp, stxname, (SELECT pg_catalog.string_agg(pg_catalog.quote_ident(attname),', ') FROM pg_catalog.unnest(stxkeys) s(attnum) JOIN pg_catalog.pg_attribute a ON (stxrelid = a.attrelid AND a.attnum = s.attnum AND NOT attisdropped)) AS columns, 'd' = any(stxkind) AS ndist_enabled, 'f' = any(stxkind) AS deps_enabled FROM pg_catalog.pg_statistic_ext stat WHERE stxrelid = '16410' ORDER BY 1; ************************** ********* QUERY ********** SELECT pubname FROM pg_catalog.pg_publication p JOIN pg_catalog.pg_publication_rel pr ON p.oid = pr.prpubid WHERE pr.prrelid = '16410' UNION ALL SELECT pubname FROM pg_catalog.pg_publication p WHERE p.puballtables AND pg_catalog.pg_relation_is_publishable('16410') ORDER BY 1; ************************** ********* QUERY ********** SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = '16410' AND c.relkind != 'p' ORDER BY inhseqno; ************************** ********* QUERY ********** SELECT c.oid::pg_catalog.regclass, pg_catalog.pg_get_expr(c.relpartbound, c.oid) FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '16410' ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text; ************************** Table "public.foo" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- id | integer | | | Policies (forced row security enabled): POLICY "p1" USING ((id <> 42)) test=# 8<-------------------------- Hope this helps, Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development Attachments: [application/pgp-signature] signature.asc (819B, ../../[email protected]/2-signature.asc) download ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Sqlectron @ 2018-01-22 21:38 Brice Maron <[email protected]> parent: Joe Conway <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Brice Maron @ 2018-01-22 21:38 UTC (permalink / raw) To: Joe Conway <[email protected]>; +Cc: [email protected] Nice ! i didn't know that one ... it will be handy :) but what i was really looking for is something more in the shape of : > Create table foo (id integer, ...., ); as pg_dump can give you 2018-01-22 21:52 GMT+01:00 Joe Conway <[email protected]>: > On 01/22/2018 02:57 PM, Brice Maron wrote: > > I'm new on this ML and I'm currently working on the multi-database > > client written using React and electron that you can see here : > > https://sqlectron.github.io/. > > especially on the postgresql part. > > > > I wanted to improve support on DDL extraction of the tables (like create > > table with all comments, PK, FK, ....) ; but it seems that there is no > > easy way to get a textual representation of this in sql, am i correct ? > > If you start the command line psql client with the -E option it will > show you the internal queries used to gather and show information with > the "slash" commands, e.g. \d <tablename> > > Here is what it looks like: > > 8<-------------------------- > psql -E test > psql (10.1) > Type "help" for help. > > test=# \d foo > ********* QUERY ********** > SELECT c.oid, > n.nspname, > c.relname > FROM pg_catalog.pg_class c > LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace > WHERE c.relname ~ '^(foo)$' > AND pg_catalog.pg_table_is_visible(c.oid) > ORDER BY 2, 3; > ************************** > > ********* QUERY ********** > SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, > c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, c.relhasoids, > '', c.reltablespace, CASE WHEN c.reloftype = 0 THEN '' ELSE > c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relpersistence, > c.relreplident > FROM pg_catalog.pg_class c > LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid) > WHERE c.oid = '16410'; > ************************** > > ********* QUERY ********** > SELECT a.attname, > pg_catalog.format_type(a.atttypid, a.atttypmod), > (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128) > FROM pg_catalog.pg_attrdef d > WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef), > a.attnotnull, a.attnum, > (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t > WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND > a.attcollation <> t.typcollation) AS attcollation, > a.attidentity, > NULL AS indexdef, > NULL AS attfdwoptions > FROM pg_catalog.pg_attribute a > WHERE a.attrelid = '16410' AND a.attnum > 0 AND NOT a.attisdropped > ORDER BY a.attnum; > ************************** > > ********* QUERY ********** > SELECT inhparent::pg_catalog.regclass, > pg_catalog.pg_get_expr(c.relpartbound, inhrelid) > FROM pg_catalog.pg_class c JOIN pg_catalog.pg_inherits i ON c.oid = > inhrelid > WHERE c.oid = '16410' AND c.relispartition; > ************************** > > ********* QUERY ********** > SELECT pol.polname, pol.polpermissive, > CASE WHEN pol.polroles = '{0}' THEN NULL ELSE > pg_catalog.array_to_string(array(select rolname from pg_catalog.pg_roles > where oid = any (pol.polroles) order by 1),',') END, > pg_catalog.pg_get_expr(pol.polqual, pol.polrelid), > pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid), > CASE pol.polcmd > WHEN 'r' THEN 'SELECT' > WHEN 'a' THEN 'INSERT' > WHEN 'w' THEN 'UPDATE' > WHEN 'd' THEN 'DELETE' > END AS cmd > FROM pg_catalog.pg_policy pol > WHERE pol.polrelid = '16410' ORDER BY 1; > ************************** > > ********* QUERY ********** > SELECT oid, stxrelid::pg_catalog.regclass, > stxnamespace::pg_catalog.regnamespace AS nsp, stxname, > (SELECT pg_catalog.string_agg(pg_catalog.quote_ident(attname),', ') > FROM pg_catalog.unnest(stxkeys) s(attnum) > JOIN pg_catalog.pg_attribute a ON (stxrelid = a.attrelid AND > a.attnum = s.attnum AND NOT attisdropped)) AS columns, > 'd' = any(stxkind) AS ndist_enabled, > 'f' = any(stxkind) AS deps_enabled > FROM pg_catalog.pg_statistic_ext stat WHERE stxrelid = '16410' > ORDER BY 1; > ************************** > > ********* QUERY ********** > SELECT pubname > FROM pg_catalog.pg_publication p > JOIN pg_catalog.pg_publication_rel pr ON p.oid = pr.prpubid > WHERE pr.prrelid = '16410' > UNION ALL > SELECT pubname > FROM pg_catalog.pg_publication p > WHERE p.puballtables AND pg_catalog.pg_relation_is_publishable('16410') > ORDER BY 1; > ************************** > > ********* QUERY ********** > SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, > pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = > '16410' AND c.relkind != 'p' ORDER BY inhseqno; > ************************** > > ********* QUERY ********** > SELECT c.oid::pg_catalog.regclass, > pg_catalog.pg_get_expr(c.relpartbound, c.oid) FROM pg_catalog.pg_class > c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = > '16410' ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text; > ************************** > > Table "public.foo" > Column | Type | Collation | Nullable | Default > --------+---------+-----------+----------+--------- > id | integer | | | > Policies (forced row security enabled): > POLICY "p1" > USING ((id <> 42)) > > test=# > 8<-------------------------- > > Hope this helps, > > Joe > > -- > Crunchy Data - http://crunchydata.com > PostgreSQL Support for Secure Enterprises > Consulting, Training, & Open Source Development > > ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Sqlectron @ 2018-01-22 22:04 Joe Conway <[email protected]> parent: Brice Maron <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Joe Conway @ 2018-01-22 22:04 UTC (permalink / raw) To: Brice Maron <[email protected]>; +Cc: [email protected] On 01/22/2018 04:38 PM, Brice Maron wrote: > but what i was really looking for is something more in the shape of : >> Create table foo (id integer, ...., ); > as pg_dump can give you Ah, for that you probably need to look through pg_dump source code, or perhaps pgadmin source code for a head start. There are also functions here: https://www.postgresql.org/docs/10/static/functions-info.html But really the simplest answer in my experience is to use pg_dump with --schema-only and --table=table options. Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development Attachments: [application/pgp-signature] signature.asc (819B, ../../[email protected]/2-signature.asc) download ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2018-01-22 22:04 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2018-01-22 19:57 Sqlectron Brice Maron <[email protected]> 2018-01-22 20:52 ` Joe Conway <[email protected]> 2018-01-22 21:38 ` Brice Maron <[email protected]> 2018-01-22 22:04 ` Joe Conway <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox