Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1edjoJ-0003Cm-Od for pgsql-gui-dev@arkaria.postgresql.org; Mon, 22 Jan 2018 21:39:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1edjoJ-0007jb-At for pgsql-gui-dev@arkaria.postgresql.org; Mon, 22 Jan 2018 21:39:23 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1edjoI-0007jM-SR for pgsql-gui-dev@lists.postgresql.org; Mon, 22 Jan 2018 21:39:23 +0000 Received: from mail-qt0-x233.google.com ([2607:f8b0:400d:c0d::233]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1edjoF-0001vJ-KE for pgsql-gui-dev@lists.postgresql.org; Mon, 22 Jan 2018 21:39:21 +0000 Received: by mail-qt0-x233.google.com with SMTP id x27so24711038qtm.12 for ; Mon, 22 Jan 2018 13:39:19 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:sender:in-reply-to:references:from:date:message-id :subject:to:cc; bh=cKq9AGSEctc9kAA28llqj71PYumxBE3NHUM0mDhEVAM=; b=erZ0Kh/rtDY4Q7QlZFRjoDd51Rz6o9tc2t7mh2jyMmK6lJWMflC8qUNumLEJJjm4Nm ZW5aBWbSHrIO/YZb7Xy7sCRv3pH9LMOO1+3MAeKejwZoKN2F1NnlXUwNpbVkRS/sIouj ourl1g+3sMvfT3wcRgSNirmy4tHs8Z0D3MfNbLZ8GLTKuRVPqfWR7bA//aI99myPb86E rvFGIFSbPlWpNEUT6zGX0OUeb1hP00xwJLuAftp0gxqHNw/M6buQuVTZmoQxF88cEqpu vLjLweutcdEi0OfHsl9psi/5dTzMTDZhneX84dlnxBSWr3E1KlrEtwiqdoUiqYwfmkmb Uu8w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:sender:in-reply-to:references:from :date:message-id:subject:to:cc; bh=cKq9AGSEctc9kAA28llqj71PYumxBE3NHUM0mDhEVAM=; b=mtvAJfdGp4QGcE7neZjDAA0z6jEbsHq3qjxs/5eJfAnb8a+amLAeJRJdUmHJ9wClzD Ki6cTGUPik2uh2r73rUZDSwfgUHYWCP8iWDR+9A3VoAKjNfFMNTtdmfF430IvSVKtOUN di3CpoozJKSsNvKqxVvlqflsvqh5k0djU2shUcrOnfh90aB/1trmf4qd7gi6cDHxgCZv Al+AsjmFAM6NhUazk4a3leOlfxcplQF9WQkpXXJHVfj9/J2MY/I/H91SGGA9ov0O9LD8 O7LknMIAjA9i1/0ckeLkLsLBni/aA8KDb6AdcpkD6mKKIKU/EU3YYvWf85H8CM4ruNqE /+Pg== X-Gm-Message-State: AKwxytcxPAD++5DkNQQyNx85GWQw2CSTAPGDsFPMPTZRm4e/gKVstLSp +yT9j2qmsfDTLJl46+HhKyloZmLLguF4xO5IEiFBjg== X-Google-Smtp-Source: AH8x224zY2ere0unb74kTDzRm4Urc91qOC+HQ4avX92MMhI5obLn84rQbFEBMZBaUK7xdVr2I9rMn+5Uy2Bu7YcjVPs= X-Received: by 10.237.42.165 with SMTP id t34mr440358qtd.60.1516657158596; Mon, 22 Jan 2018 13:39:18 -0800 (PST) MIME-Version: 1.0 Sender: merzhin@gmail.com Received: by 10.12.171.3 with HTTP; Mon, 22 Jan 2018 13:38:58 -0800 (PST) In-Reply-To: References: From: Brice Maron Date: Mon, 22 Jan 2018 22:38:58 +0100 X-Google-Sender-Auth: eJlnbTsXuitqLEiRSnQvFnxkunI Message-ID: Subject: Re: Sqlectron To: Joe Conway Cc: pgsql-gui-dev@lists.postgresql.org Content-Type: multipart/alternative; boundary="94eb2c06a3b02f2e74056364435e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --94eb2c06a3b02f2e74056364435e Content-Type: text/plain; charset="UTF-8" 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 : > 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 > > 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 > > --94eb2c06a3b02f2e74056364435e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Nice ! i didn't know that one ... it wi= ll 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

<= div class=3D"gmail_quote">2018-01-22 21:52 GMT+01:00 Joe Conway <mail@j= oeconway.com>:
On 01/22/2018 02:57 PM, Brice Maron wrote:
> I'm new on this ML and I'm currently working on the multi-data= base
> 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 crea= te
> 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=3D# \d foo
********* QUERY **********
SELECT c.oid,
=C2=A0 n.nspname,
=C2=A0 c.relname
FROM pg_catalog.pg_class c
=C2=A0 =C2=A0 =C2=A0LEFT JOIN pg_catalog.pg_namespace n ON n.oid =3D c.reln= amespace
WHERE c.relname ~ '^(foo)$'
=C2=A0 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 =3D 0 THEN '' EL= SE
c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relpersistence= ,
c.relreplident
FROM pg_catalog.pg_class c
=C2=A0LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid =3D tc.oid)
WHERE c.oid =3D '16410';
**************************

********* QUERY **********
SELECT a.attname,
=C2=A0 pg_catalog.format_type(a.atttypid, a.atttypmod),
=C2=A0 (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) fo= r 128)
=C2=A0 =C2=A0FROM pg_catalog.pg_attrdef d
=C2=A0 =C2=A0WHERE d.adrelid =3D a.attrelid AND d.adnum =3D a.attnum AND a.= atthasdef),
=C2=A0 a.attnotnull, a.attnum,
=C2=A0 (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_typ= e t
=C2=A0 =C2=A0WHERE c.oid =3D a.attcollation AND t.oid =3D a.atttypid AND a.attcollation <> t.typcollation) AS attcollation,
=C2=A0 a.attidentity,
=C2=A0 NULL AS indexdef,
=C2=A0 NULL AS attfdwoptions
FROM pg_catalog.pg_attribute a
WHERE a.attrelid =3D '16410' AND a.attnum > 0 AND NOT a.attisdro= pped
ORDER BY a.attnum;
**************************

********* QUERY **********
SELECT inhparent::pg_catalog.regclass,
=C2=A0 pg_catalog.pg_get_expr(c.relpartbound, inhrelid)
FROM pg_catalog.pg_class c JOIN pg_catalog.pg_inherits i ON c.oid =3D inhre= lid
WHERE c.oid =3D '16410' AND c.relispartition;
**************************

********* QUERY **********
SELECT pol.polname, pol.polpermissive,
=C2=A0 CASE WHEN pol.polroles =3D '{0}' THEN NULL ELSE
pg_catalog.array_to_string(array(select rolname from pg_catalog.pg_rol= es
where oid =3D any (pol.polroles) order by 1),',') END,
=C2=A0 pg_catalog.pg_get_expr(pol.polqual, pol.polrelid),
=C2=A0 pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid),
=C2=A0 CASE pol.polcmd
=C2=A0 =C2=A0 WHEN 'r' THEN 'SELECT'
=C2=A0 =C2=A0 WHEN 'a' THEN 'INSERT'
=C2=A0 =C2=A0 WHEN 'w' THEN 'UPDATE'
=C2=A0 =C2=A0 WHEN 'd' THEN 'DELETE'
=C2=A0 =C2=A0 END AS cmd
FROM pg_catalog.pg_policy pol
WHERE pol.polrelid =3D '16410' ORDER BY 1;
**************************

********* QUERY **********
SELECT oid, stxrelid::pg_catalog.regclass,
stxnamespace::pg_catalog.regnamespace AS nsp, stxname,
=C2=A0 (SELECT pg_catalog.string_agg(pg_catalog.quote_ident(attname),&= #39;, ')
=C2=A0 =C2=A0FROM pg_catalog.unnest(stxkeys) s(attnum)
=C2=A0 =C2=A0JOIN pg_catalog.pg_attribute a ON (stxrelid =3D a.attrelid AND=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 a.attnum =3D s.attnum AND NOT attisdropped)) AS= columns,
=C2=A0 'd' =3D any(stxkind) AS ndist_enabled,
=C2=A0 'f' =3D any(stxkind) AS deps_enabled
FROM pg_catalog.pg_statistic_ext stat WHERE stxrelid =3D '16410' ORDER BY 1;
**************************

********* QUERY **********
SELECT pubname
FROM pg_catalog.pg_publication p
JOIN pg_catalog.pg_publication_rel pr ON p.oid =3D pr.prpubid
WHERE pr.prrelid =3D '16410'
UNION ALL
SELECT pubname
FROM pg_catalog.pg_publication p
WHERE p.puballtables AND pg_catalog.pg_relation_is_publishable('16= 410')
ORDER BY 1;
**************************

********* QUERY **********
SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c,
pg_catalog.pg_inherits i WHERE c.oid=3Di.inhparent AND i.inhrelid =3D
'16410' AND c.relkind !=3D '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=3Di.inhrelid AND i.inhparent =3D '16410' ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;<= br> **************************

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Table "public.= foo"
=C2=A0Column |=C2=A0 Type=C2=A0 =C2=A0| Collation | Nullable | Default
--------+---------+-----------+----------+---------
=C2=A0id=C2=A0 =C2=A0 =C2=A0| integer |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0|=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |
Policies (forced row security enabled):
=C2=A0 =C2=A0 POLICY "p1"
=C2=A0 =C2=A0 =C2=A0 USING ((id <> 42))

test=3D#
8<--------------------------

Hope this helps,

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development


--94eb2c06a3b02f2e74056364435e--