2

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?

1
  • Perhaps you want to use %s when printing the table name. This seems related.
    – mustaccio
    Dec 17, 2023 at 12:48

1 Answer 1

1

\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:

3
  • Thanks. It works perfectly except :'schema'. I have to write :schema. About coalesce(character_maximum_length, numeric_precision) : you're right, it's odd. But when column is a character string, length is in the first but when an integer, it seems to be in the second. In fact, that's not very important at this stage (for me... It's a kind of POC) but i'll have a look at pg_attribute (but i find pg_catalog much more complicated than information_schema :-) )
    – Jice
    Dec 18, 2023 at 15:30
  • @Jice: The safe way is to pass the schema name unquoted and use :'schema' for interpolation. This handles possible single quotes in the name reliably. Else it's a possible entry vector for SQL injection. Dec 18, 2023 at 19:45
  • Ah yes, i see. You're right. I'll do it
    – Jice
    Dec 22, 2023 at 14:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.