Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id B0B004762DA for ; Mon, 25 Nov 2002 11:21:10 -0500 (EST) Received: from post.webmailer.de (natsmtp01.webmailer.de [192.67.198.81]) by postgresql.org (Postfix) with ESMTP id 91F9E476296 for ; Mon, 25 Nov 2002 11:21:09 -0500 (EST) Received: from dell (p50915801.dip.t-dialin.net [80.145.88.1]) by post.webmailer.de (8.9.3/8.8.7) with ESMTP id RAA27860 for ; Mon, 25 Nov 2002 17:21:10 +0100 (MET) Content-Type: text/plain; charset="us-ascii" From: Tilo Schwarz To: pgsql-sql@postgresql.org Subject: Question on SQL and pg_-tables Date: Mon, 25 Nov 2002 17:21:27 +0100 User-Agent: KMail/1.4.3 MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200211251721.27543.mail@tilo-schwarz.de> X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200211/352 X-Sequence-Number: 10494 Dear all, after reading about the pg_* system tables, I made up a view to see the all= =20 user columns, their type, default value, indices etc. at once (see example= =20 below). Now my first question is: - Is it possible to get the same result with a simpler / shorter SQL query= =20 than shown below (I'm not so familiar with SQL yet, hopefully that query is= =20 correct in the first place...)? I was also trying to get the RI-Constraints out of the pg_* tables. I found= =20 the two tables involved in a RI-Constraint in pg_trigger (tgrelid,=20 tgconstrrelid), but the affected columns are only(?) in the tgargs. To get= =20 them out of tgargs, I need some (easy) string processing. My second questio= n=20 is: - Is it possible to get not only the two tables, but also their correspondi= ng=20 two columns involved in a RI-Constraint out of the pg_* tables just with a= =20 SQL query? Thanks for any comments! Tilo Example and view definition for question one: Example: testobj=3D> select * from columns; table | column | type | len | notnull | dims | default_va= lue=20=20=20=20=20=20=20=20=20=20 | index | primary | unique -------------+----------+------+-----+---------+------+--------------------= -------------+----------------------+---------+-------- bbox | box | box | 32 | f | 0 |=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20 | bbox_area | f | f bbox | box | box | 32 | f | 0 |=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20 | bbox_box | f | f bbox | box | box | 32 | f | 0 |=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20 | bbox_height | f | f bbox | box | box | 32 | f | 0 |=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20 | bbox_width | f | f bbox | id | int4 | 4 | t | 0 |=20 nextval('"bbox_id_seq"'::text) | bbox_pkey | t | t bbox | obj_id | int4 | 4 | f | 0 |=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20 | bbox_obj_id | f | f label | descr | text | -1 | f | 0 |=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20 | | | label | id | int4 | 4 | t | 0 |=20 nextval('"label_id_seq"'::text) | label_pkey | t | t obj | id | int4 | 4 | t | 0 |=20 nextval('"obj_id_seq"'::text) | obj_pkey | t | t obj_label_r | label_id | int4 | 4 | f | 0 |=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20 | obj_label_r_label_id | f | f obj_label_r | obj_id | int4 | 4 | f | 0 |=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20 | obj_label_r_obj_id | f | f test2 | a | int4 | 4 | f | 0 |=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20 | test2id | f | f test2 | b | int4 | 4 | f | 0 |=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20 | | | test2 | c | int4 | 4 | f | 0 |=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20 | test2id | f | f test2 | d | int4 | 4 | f | 0 |=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20 | | | test2 | e | int4 | 4 | f | 0 |=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20 | test2id | f | f (16 rows) View definition: CREATE VIEW columns as=20 select defj.relname as table, defj.attname as column, defj.typname as type, defj.attlen as len, defj.attnotnull as notnull, defj.attndims as dims, defj.adsrc as default_value, indj.relname as index, indj.indisprimary as primary, indj.indisunique as unique from -- first get all user columns for all user tables ((select * from=20 pg_class, pg_attribute, pg_type where pg_class.oid =3D attrelid and pg_type.oid =3D atttypid and relname !~ 'pg_' and relname !~ 'pga_' and pg_class.relkind =3D 'r' and pg_attribute.attnum > 0) as colj -- then get possible default values left outer join pg_attrdef on attrelid =3D adrelid and attnum =3D adnum) as defj -- then get possible indices left outer join (select * from pg_class, pg_index, pg_attribute where pg_class.oid =3D indexrelid and pg_class.oid =3D attrelid) as indj on (defj.attrelid =3D indj.indrelid and defj.attnum =3D indj.indkey[indj.attnum-1]) order by 1, 2, index;