Re: How does psql actually implement the \d commands - Mailing list pgsql-general

On Apr 9, 5:51 am, laurenz.a...@wien.gv.at ("Albe Laurenz") wrote:
> Andrew Falanga wrote:
>
> > I know about the -E option to psql and did that to get the following,
> > which is what psql does for a \d <tablename>:
>
> > ********* 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 pg_catalog.pg_table_is_visible(c.oid)
> >       AND c.relname ~ '^(personaldata)$'
> > ORDER BY 2, 3;
> > **************************
>
> > ********* QUERY **********
> > SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules
> > FROM pg_catalog.pg_class WHERE oid = '17408'
> > **************************
>
> > ********* QUERY **********
> > SELECT a.attname,
> >   pg_catalog.format_type(a.atttypid, a.atttypmod),
> >   (SELECT substring(d.adsrc 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
> > FROM pg_catalog.pg_attribute a
> > WHERE a.attrelid = '17408' AND a.attnum > 0 AND NOT a.attisdropped
> > ORDER BY a.attnum
> > **************************
>
> > ********* QUERY **********
> > SELECT c.relname FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
> > WHERE c.oid=i.inhparent AND i.inhrelid = '17408' ORDER BY inhseqno ASC
> > **************************
>
> > Now, I tried to execute these queries one at a time and they failed,
> > somewhat miserably.  In what order does PostgreSQL actually execute
> > them?  Are they implemented as sub-queries?  If so, in what order are
> > they executed?
>
> They do not fail here, and they should not fail.
> They should be executed as above, in this order.
>
> What are the miserable error messages you get?
>
> Yours,
> Laurenz Albe
>
> --
> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general

Sorry for the long delay in responding, lot's happening now.

Ok, here's what I get (output from psql):

mch=# SELECT c.oid,
mch-#   n.nspname,
mch-#   c.relname
mch-# FROM pg_catalog.pg_class c
mch-#      LEFT JOIN pg_catalog.pg_namespace n ON n.oid =
c.relnamespace
mch-# WHERE pg_catalog.pg_table_is_visible(c.oid)
mch-#       AND c.relname ~ '^(personaldata)$'
mch-# ORDER BY 2, 3;
  oid  | nspname |   relname
-------+---------+--------------
 17408 | public  | personaldata
(1 row)

mch=# SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules
mch-# FROM pg_catalog.pg_class WHERE oid = '17408' ;
 relhasindex | relkind | relchecks | reltriggers | relhasrules
-------------+---------+-----------+-------------+-------------
 f           | r       |         0 |           0 | f
(1 row)

mch=# SELECT a.attname,
mch-#   pg_catalog.format_type(a.atttypid, a.atttypmod),
mch-#   (SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef
d
mch(#    WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND
mch(# a.atthasdef),
mch-#   a.attnotnull, a.attnum
mch-# FROM pg_catalog.pg_attribute a
mch-# WHERE a.attrelid = '17408' AND a.attnum > 0 AND NOT
a.attisdropped
mch-# ORDER BY a.attnum ;
      attname      |      format_type       | ?column? | attnotnull |
attnum
-------------------+------------------------+----------+------------
+--------
 odn               | integer                |          | f
|      1
 placeofbirth      | character varying(40)  |          | f
|      2
 ps                | character varying(50)  |          | f
|      3
 po                | character varying(50)  |          | f
|      4
 village           | character varying(50)  |          | f
|      5
 lastname          | character varying(50)  |          | f
|      6
 firstname         | character varying(50)  |          | f
|      7
 address           | character varying(100) |          | f
|      8
 father_lastname   | character varying(50)  |          | f
|      9
 father_firstname  | character varying(50)  |          | f
|     10
 husband_lastname  | character varying(50)  |          | f
|     11
 husband_firstname | character varying(50)  |          | f
|     12
 billingaddress    | character varying(50)  |          | f
|     13
 nationality       | character varying(50)  |          | f
|     14
 jat               | character varying(50)  |          | f
|     15
 religion          | character varying(25)  |          | f
|     16
 occupation        | character varying(50)  |          | f
|     17
 age               | integer                |          | f
|     18
 sex               | character(1)           |          | f
|     19
(19 rows)

mch=# SELECT c.relname FROM pg_catalog.pg_class c,
pg_catalog.pg_inherits i
mch-# WHERE c.oid=i.inhparent AND i.inhrelid = '17408' ORDER BY
inhseqno ASC ;
 relname
---------
(0 rows)

So, obviously, I was doing something wrong because when I tried this
before, the queries failed.  I don't now know what I was doing wrong,
but obviously, I was doing something wrong.  So, I guess the only
question I have now is, since PostreSQL uses these four queries to
display the output from \d <tablename>, what does Postgres do
internally that makes the output look like this:

              Table "public.personaldata"
      Column       |          Type          | Modifiers
-------------------+------------------------+-----------
 odn               | integer                |
 placeofbirth      | character varying(40)  |
 ps                | character varying(50)  |
 po                | character varying(50)  |
 village           | character varying(50)  |
 lastname          | character varying(50)  |
 firstname         | character varying(50)  |
 address           | character varying(100) |
 father_lastname   | character varying(50)  |
 father_firstname  | character varying(50)  |
 husband_lastname  | character varying(50)  |
 husband_firstname | character varying(50)  |
 billingaddress    | character varying(50)  |
 nationality       | character varying(50)  |
 jat               | character varying(50)  |
 religion          | character varying(25)  |
 occupation        | character varying(50)  |
 age               | integer                |
 sex               | character(1)           |


???????


Thanks,
Andy