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

On Mon, 7 Nov 2011 23:20:04 +0100
"Jean-Yves F. Barbier" <12ukwn@gmail.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;

--