public inbox for [email protected]
help / color / mirror / Atom feedSqlectron
4+ messages / 2 participants
[nested] [flat]
* Sqlectron
@ 2018-01-22 19:57 Brice Maron <[email protected]>
2018-01-22 20:52 ` Re: Sqlectron Joe Conway <[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 19:57 Sqlectron Brice Maron <[email protected]>
@ 2018-01-22 20:52 ` Joe Conway <[email protected]>
2018-01-22 21:38 ` Re: Sqlectron 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 19:57 Sqlectron Brice Maron <[email protected]>
2018-01-22 20:52 ` Re: Sqlectron Joe Conway <[email protected]>
@ 2018-01-22 21:38 ` Brice Maron <[email protected]>
2018-01-22 22:04 ` Re: Sqlectron 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 19:57 Sqlectron Brice Maron <[email protected]>
2018-01-22 20:52 ` Re: Sqlectron Joe Conway <[email protected]>
2018-01-22 21:38 ` Re: Sqlectron Brice Maron <[email protected]>
@ 2018-01-22 22:04 ` Joe Conway <[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