I'm spending some time trying to get to grips with the pg_catalog tables. I'm assuming the names and such come back from the days when letters had to be carved individually and were, consequently, very expensive ;-) Anyway, that's all a fact of life. I wanted to get the default value for a column and ran into this thread. I saw a mention of wanting to have Erwin Brandstetter's code as a function. I've wrapped it up and figured I'd add it to the archives:
CREATE OR REPLACE FUNCTION data.column_default (qualified_name text, column_name text)
RETURNS text
AS $$
SELECT d.adsrc AS default_value -- A human-readable representation of the default value, already typed as text.
FROM pg_catalog.pg_attribute a
LEFT JOIN pg_catalog.pg_attrdef d ON (a.attrelid, a.attnum)
= (d.adrelid, d.adnum)
WHERE NOT a.attisdropped -- no dropped (dead) columns
AND a.attnum > 0 -- no system columns
AND a.attrelid = qualified_name::regclass
AND a.attname = column_name;
$$ LANGUAGE sql;
ALTER FUNCTION data.column_default (qualified_name text, column_name text) OWNER TO user_bender;
I'm using the code (probably in an inhumane manner) like so:
select pg_class.relnamespace::regnamespace as schema_name,
attrelid::regclass as parent_name,
attname,
format_type(atttypid, atttypmod) as data_type,
column_default(attrelid::regclass::text,attname),
attnum
from pg_attribute
left join pg_class on (pg_class.oid = pg_attribute.attrelid::regclass)
From here I figure I'll write a view, etc., once I know better what I'm really after. For now, I'm using a CTE as a temporary view:
with attributes as
(select pg_class.relnamespace::regnamespace as schema_name,
attrelid::regclass as parent_name,
attname,
format_type(atttypid, atttypmod) as data_type,
column_default(attrelid::regclass::text,attname),
attnum
from pg_attribute
left join pg_class on (pg_class.oid = pg_attribute.attrelid::regclass)
)
select *
from attributes
where parent_name::text = 'sales'
Corrections, improvements, and suggestions all welcome...I'm just getting my feet wet in pg_catalog.
