Thread: Dumping SQL type names

Dumping SQL type names

From
Peter Eisentraut
Date:
A while ago I suggested to create a backend internal function that takes
an internal type name and an atttypmod and formats it to a canonical SQL
type, to be used by pg_dump, psql, and anyone who feels like it.

I have it written now, here's an example of how it works. Any comments?


regression=# select c.relname, a.attname, format_type(t.typname, a.atttypmod)
from pg_class c, pg_attribute a, pg_type t
where c.oid=a.attrelid and t.oid=a.atttypid
and c.relname not like 'pg\_%' and a.attnum > 0
order by 1;
        relname         |    attname    |      format_type
-------------------------+---------------+------------------------a                       | aa            | texta_star
               | class         | character(1)a_star                  | aa            | integera_star                  |
a            | textabstime_tbl             | f1            | abstimeaggtest                 | a             |
smallintaggtest                | b             | realarrtest                 | a             | smallint[]arrtest
        | b             | integer[]arrtest                 | c             | name[]arrtest                 | d
  | text[]arrtest                 | e             | double precision[]arrtest                 | f             |
character(5)[]arrtest                | g             | character varying(5)[]b                       | aa            |
text
...

-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden




Re: Dumping SQL type names

From
Tom Lane
Date:
Peter Eisentraut <peter_e@gmx.net> writes:
> A while ago I suggested to create a backend internal function that takes
> an internal type name and an atttypmod and formats it to a canonical SQL
> type, to be used by pg_dump, psql, and anyone who feels like it.

> I have it written now, here's an example of how it works. Any comments?

For the most part, a function working from type OID would be more
convenient, I should think.  Possibly offer both ...
        regards, tom lane