Archive for February, 2013

User Defined Type Definition

I get to do this all the time and I thought it would be a good addition to this blog. My case scenario here is that every time there is a db release going out that modifies a user defined type in my database I get nervous because I need to have a quick rollback plan if the release is called for a rollback. There may be a better way to achieve this feel free to share. It can come in handy if you really need to know what CREATE TYPE syntax is being used.

We do know that just using \dT+ might give some information but it does not give the create statement / complete definition of the type:

\dT+ accounts.billing_info
List of data types
Schema | Name | Internal name | Size | Elements | Description
-----------+------------------------------+--------------------+-------+----------+-------------
accounts | accounts.billing_info | billing_info | tuple | |
(1 row)

To get around this I use the following query that gives me the complete definition of the user defined type. Replace schema and table name in the below to match your requirement.

SELECT 'CREATE TYPE accounts.billing_info as ('||array_to_string(ARRAY(select a.attname||' '|| pg_catalog.format_type(a.atttypid, a.atttypmod)
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = (select oid from pg_class where relname='billing_info' and relnamespace=(select oid from pg_namespace where nspname='accounts'))
AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum),',')||');';

?column?

--------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TYPE accounts.billing_info as (billme character varying(20),serial integer,bill_name character varying(100));
(1 row)

And here I have my complete type definition and can use it as required. Hope this helps!!!

Advertisements

,

Leave a comment

%d bloggers like this: