Skip site navigation (1) Skip section navigation (2)

Re: how to find details of a domain type? - [SOLVED]

From: "Jean-Yves F(dot) Barbier" <12ukwn(at)gmail(dot)com>
To: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: how to find details of a domain type? - [SOLVED]
Date: 2011-11-08 03:39:15
Message-ID: 20111108043915.277bfeae@anubis.defcon1 (view raw or flat)
Thread:
Lists: pgsql-novice
On Mon, 7 Nov 2011 23:20:04 +0100
"Jean-Yves F. Barbier" <12ukwn(at)gmail(dot)com> wrote:

... 
> However, I don't see how to extract 'numeric(7, 6)' from '458762' (IF it
> should be extract from that value!)
> 
> How can I do that?
> 
> JY

Ok, for who's interested I finally extract it from the logs of psql '\dD'
(hopefully, because if I had to find it myself Pg would be v.25.0 the 
time I figure this:(


/*
Call:   SELECT * FROM e_dom()
            AS z(domschema NAME, domname NAME, domtype TEXT,
            domodifier TEXT, domcheck TEXT);
*/
--=============================================================================
CREATE OR REPLACE FUNCTION e_dom()
RETURNS SETOF RECORD AS $$
    SELECT N.nspname AS domschema, T.typname AS domname,
        pg_catalog.format_type(T.typbasetype, T.typtypmod) AS domtype,
        TRIM(LEADING
            COALESCE((SELECT ' collate ' || C.collname 
                    FROM pg_catalog.pg_collation C, pg_catalog.pg_type CT
                    WHERE C.oid = T.typcollation AND CT.oid = T.typbasetype 
                        AND T.typcollation <> CT.typcollation), '') 
                    ||
                    CASE WHEN T.typnotnull THEN 
                        ' not null' 
                    ELSE 
                        ''
                    END 
                    ||
                    CASE WHEN T.typdefault IS NOT NULL THEN 
                        ' default ' || T.typdefault 
                    ELSE 
                        ''
                    END) AS domodifier,
        pg_catalog.array_to_string(ARRAY(SELECT pg_catalog.pg_get_constraintdef(r.oid, true)
                                        FROM pg_catalog.pg_constraint R
                                        WHERE T.oid = R.contypid), ' ') AS domcheck
    FROM pg_catalog.pg_type T
    LEFT JOIN pg_catalog.pg_namespace N ON N.oid = T.typnamespace
    WHERE T.typtype = 'd'
        AND N.nspname <> 'pg_catalog'
        AND N.nspname <> 'information_schema'
        AND pg_catalog.pg_type_is_visible(T.oid)
    ORDER BY 1, 2;
$$ LANGUAGE sql STRICT SECURITY DEFINER STABLE;

-- 

In response to

pgsql-novice by date

Next:From: Thom BrownDate: 2011-11-08 08:24:05
Subject: Re: Copy rows returned from a view into a table in a different db
Previous:From: Jean-Yves F. BarbierDate: 2011-11-07 22:20:04
Subject: how to find details of a domain type?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group