pg_query show/describe tables

Question

Is there a way to show all tables, describe them \d and dump the result on php?

Any ideas will be appreciated

Answer 1

You can get the data from information_schema.tables.

Answer 2

Yea, Meta information about your database is stored in pg_catalog.

Answer 3

Here's what I see in the logs when I run \d in a psql shell:

SELECT n.nspname as "Schema",
      c.relname as "Name",
      CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
      r.rolname as "Owner"
    FROM pg_catalog.pg_class c
         JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
         LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    WHERE c.relkind IN ('r','v','S','')
          AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
      AND pg_catalog.pg_table_is_visible(c.oid)
    ORDER BY 1,2;

Answer 4

Run psql -E, then enter all interesting \x commands (like \dt, \d table), and read what it will show.

Answer 5

There is also pg_dump --schema-only.