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

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>:
> >
[...]
> >
> > > 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?
>
> Ok, here's what I get (output from psql):
>
[...]
>
> 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
> 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)
>
[...]
>
> 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)           |

Format the output.

For example, the "17408" in the query above is a result from the
first query.

If you had triggers, constraints, rules or indexes associated
with the table or the table would INHERIT another table, you'd probably
see much more clearly what the other queries do.

Yours,
Laurenz Albe