Using results of \d command in psql

Question

The command \d in psql lists all tables, views, and sequences. Is there a way to catch this output into a table and run a query on it?

mydb-> \d

      List of relations
Schema |  Name  | Type  |   Owner
-------+--------+-------+---------
public | mytab1 | table | username

mydb-> select * from <use relation from \d> where Type='view';

      List of relations
Schema |  Name  | Type  |   Owner
-------+--------+-------+---------

Alternatively, is there a way to use select-from-where query to get this table and other table meta data?

Thanks.

Answer 1

You can get the query behind \d if you start psql with the -E option. Then running \d will also show the metadata query.

Once you have that query, it should be easy to adapt it to your needs.

If you need it to builld a list of DDL statements, you can use psql's \gexec to execute the results of a query as SQL statements.

Answer 2

If you want to get the query, you can run \set ECHO_HIDDEN on like in the comments above. If you do that, you'll get the query:

SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p','v','m','S','f','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

So, ya know, there ya go.