Thread: how to find details of a domain type?

Hi list,

I've a function:

SELECT D.typname as domname, format_type(b.oid,NULL) as dombasetype,
    D.typlen, D.typtypmod, D.typnotnull, D.typdefault, D.typndims, D.typdelim, C.consrc
    FROM pg_type D
    JOIN pg_type B ON B.oid = CASE WHEN B.typndims > 0 then D.typelem ELSE D.typbasetype END
    JOIN pg_namespace N ON N.oid = B.typnamespace
    JOIN pg_constraint C ON (D.typname || '_check') = C.conname
    WHERE D.typtype = 'd' AND D.typnamespace = 2200::oid ORDER BY D.typname;

that returns (not complete):

domain name    | domain type | length(+typlen) | not null? | default  | constraint
dn_percent_vat | numeric     |          458762 | t         | 0.196000 | ((VALUE > -0.000001) AND (VALUE < 1.000001))

As 'length(+typlen)' returns 20 for 'varchar(16)', and min length of type
'character varying' is 4, 20-4=16 check ok.

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
--
Here's to women.  Would that we could fall into her arms without falling
into her hands. -- Ambrose Bierce

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;

--