Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtp (Exim 4.84_2)
	(envelope-from <pgsql-gui-dev-owner+archive@lists.postgresql.org>)
	id 1edj5Y-00081i-5k
	for pgsql-gui-dev@arkaria.postgresql.org; Mon, 22 Jan 2018 20:53:08 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.84_2)
	(envelope-from <pgsql-gui-dev-owner+archive@lists.postgresql.org>)
	id 1edj5X-0004om-HZ
	for pgsql-gui-dev@arkaria.postgresql.org; Mon, 22 Jan 2018 20:53:07 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.84_2)
	(envelope-from <mail@joeconway.com>)
	id 1edj5X-0004oc-DA
	for pgsql-gui-dev@lists.postgresql.org; Mon, 22 Jan 2018 20:53:07 +0000
Received: from 104-190-1-44.lightspeed.sndgca.sbcglobal.net ([104.190.1.44]
 helo=joeconway.com)
	by magus.postgresql.org with esmtp (Exim 4.89)
	(envelope-from <mail@joeconway.com>)
	id 1edj5U-0004Ra-1V
	for pgsql-gui-dev@lists.postgresql.org; Mon, 22 Jan 2018 20:53:06 +0000
Received: from [108.225.169.28] (account jconway@joeconway.com HELO
 [192.168.1.119])
  by joeconway.com (CommuniGate Pro SMTP 6.1.4)
  with ESMTPSA id 19486405; Mon, 22 Jan 2018 12:52:54 -0800
Subject: Re: Sqlectron
To: Brice Maron <bmaron@gmail.com>, pgsql-gui-dev@lists.postgresql.org
References: 
 <CAHBQ7mdnCPsGQ5V7vHNG9MAxXnt9eNY+RJqnLsrBo2-CwwFpUw@mail.gmail.com>
From: Joe Conway <mail@joeconway.com>
Openpgp: id=4EA5B4659ED59CC9538139A5332B7E68B699434A
Message-ID: <bef58052-c7da-7f51-5038-b5cad0d463e8@joeconway.com>
Date: Mon, 22 Jan 2018 15:52:53 -0500
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.5.0
MIME-Version: 1.0
In-Reply-To: 
 <CAHBQ7mdnCPsGQ5V7vHNG9MAxXnt9eNY+RJqnLsrBo2-CwwFpUw@mail.gmail.com>
Content-Type: multipart/signed; micalg=pgp-sha256;
 protocol="application/pgp-signature";
 boundary="FjBi48Y7Cy7Lsmc3wQ93qn7RtdWM28JbH"
List-Id: <pgsql-gui-dev.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-gui-dev@lists.postgresql.org>
List-Owner: <mailto:pgsql-gui-dev-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-gui-dev>
Precedence: bulk

This is an OpenPGP/MIME signed message (RFC 4880 and 3156)
--FjBi48Y7Cy7Lsmc3wQ93qn7RtdWM28JbH
Content-Type: multipart/mixed; boundary="EdGvtd9otVoTWGXgkv25IzYmTcJb4rxGd";
 protected-headers="v1"
From: Joe Conway <mail@joeconway.com>
To: Brice Maron <bmaron@gmail.com>, pgsql-gui-dev@lists.postgresql.org
Message-ID: <bef58052-c7da-7f51-5038-b5cad0d463e8@joeconway.com>
Subject: Re: Sqlectron
References: <CAHBQ7mdnCPsGQ5V7vHNG9MAxXnt9eNY+RJqnLsrBo2-CwwFpUw@mail.gmail.com>
In-Reply-To: <CAHBQ7mdnCPsGQ5V7vHNG9MAxXnt9eNY+RJqnLsrBo2-CwwFpUw@mail.gmail.com>

--EdGvtd9otVoTWGXgkv25IzYmTcJb4rxGd
Content-Type: text/plain; charset=utf-8
Content-Language: en-US
Content-Transfer-Encoding: quoted-printable

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.
>=20
> I wanted to improve support on DDL extraction of the tables (like creat=
e
> 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,
  n.nspname,
  c.relname
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid =3D 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 =3D 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 =3D tc.oid)
WHERE c.oid =3D '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 =3D a.attrelid AND d.adnum =3D a.attnum AND a.atthasde=
f),
  a.attnotnull, a.attnum,
  (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t=

   WHERE c.oid =3D a.attcollation AND t.oid =3D 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 =3D '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 =3D inh=
relid
WHERE c.oid =3D '16410' AND c.relispartition;
**************************

********* QUERY **********
SELECT pol.polname, pol.polpermissive,
  CASE WHEN pol.polroles =3D '{0}' THEN NULL ELSE
pg_catalog.array_to_string(array(select rolname from pg_catalog.pg_roles
where oid =3D 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 =3D '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 =3D a.attrelid AND
        a.attnum =3D s.attnum AND NOT attisdropped)) AS columns,
  'd' =3D any(stxkind) AS ndist_enabled,
  '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('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=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;
**************************

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

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

Hope this helps,

Joe

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


--EdGvtd9otVoTWGXgkv25IzYmTcJb4rxGd--

--FjBi48Y7Cy7Lsmc3wQ93qn7RtdWM28JbH
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2

iQIcBAEBCAAGBQJaZk8lAAoJEDfy90M199hl1C4P/3opYxbG64akne4YW1xP8dse
aM9ivxOr1L8//N7NxQpDzJLb3OeRIhqN1Lq+TuII5J5h0Rg1Hk/qAbcQaUQG9GNb
tsMYBBVb1GQrXAvJrL29hMfMWq9e5J6HYxT/TzUCNWYzjRADrB7dAx7fq8rML0wo
Wlx8ks71RMalo5/752zIdbqutXxReOvEfywaUq/fLi+XhIyCsXZoaRZumeDBgupU
8xH1RiCRcIblpYDjX2PZ6452xFmuajWVB/kGdOqQM1MkXLUmaWGvZEn6SJ86AL/Q
HE/jdcxTsepuge7T9G4heRy8Sc21A+Iu/O03i7QLUtny8wj17j+0ixinOg8X5h94
GD9qMldAczzpT0KZhicXW/fu05TLTJz1DE40oW9k4icK/j0TrlVBJo6cpdwdzdqc
i4eByW7B+4+Vb9m99B5/2viqQP6kh0F0qZT6K1MuFe13TexJj1Dly5KT9TISzVGQ
K9DXJ2DOaYU5VwJTfUhQW7+8zIANq+rcB2v+Km9fDJfeW87lUGxpOxC92KoZWr8b
1Qr8Yo8FrqscGWL9g4bjNUI/QWBvJxSfJlPXo3IvmLfr/eQYkXrmA7ypuiRzV/RH
UilyWDGHodPuy9XZ41TCCWne8MpvaYswIfE7/MvR0fK6wAOmHjCtOY6odnJ2MMYw
bf6KHa4Jj4Ot4YmJYhHv
=ADN3
-----END PGP SIGNATURE-----

--FjBi48Y7Cy7Lsmc3wQ93qn7RtdWM28JbH--