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

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;

--