Chain 2 queries with \gexec in psql

Question

I'd like to produce a data dictionary from the catalog tables of a Postgres DB. I wanted to have the name of the table on one line and then the list of columns.
I've tried to do it with \gexec:

\set schema '''my_schema'''
\set fname 'out_file.txt'
\o :fname
SELECT format('
select ''>>> %I'';
select column_name, data_type, coalesce(character_maximum_length, numeric_precision), description
  from information_schema.columns
       left join pg_catalog.pg_description on objoid = ''%I.%I''::regclass and objsubid = ordinal_position
 where table_name = ''%I''', table_name, table_schema, table_name, table_name)
FROM 
   information_schema.tables
where table_schema = :schema
order by table_name
\gexec
\o

But the first statement seems to be omitted and the name of the table doesn't appear in the result.
Of course, I could simply add the table name in every row but for my purpose, this means an extra process after to put all this in a proper documentation.

Is there a way to have the result of the 2 queries in the result file?

Erwin Brandstetter · Accepted Answer · 2023-12-18T03:05:27.240

\gexec ...

Sends the current query buffer to the server, then treats each column of each row of the query's output (if any) as an SQL statement to be executed.

Bold emphasis mine.
Currently, you generate one column with multiple statements. Put each statement in a separate column to achieve your objective:

\set schema 'my_schema'
\set fname 'out_file.txt'
\o :fname
SELECT format('select ''>>> %I'';', table_name)
     , format('select column_name, data_type, coalesce(character_maximum_length, numeric_precision), description
               from information_schema.columns
               left join pg_catalog.pg_description on objoid = ''%I.%I''::regclass and objsubid = ordinal_position
               where table_name = %L', table_schema, table_name, table_name)
FROM   information_schema.tables
WHERE  table_schema = :'schema'
ORDER  BY table_name\gexec
\o

Note some minor fixes to quoting names.

Aside

coalesce(character_maximum_length, numeric_precision) seems odd: it's lumping two quite distinct numbers together.
Also, I would rather query the system catalog pg_attribute to get better information for types and storage - among other things. Related:

Chain 2 queries with \gexec in psql

1 Answers1

Aside