Re: Can I get the default value for an attribute (field) ?

On Sun, 1 Apr 2001, Joel Burton wrote:

> I should have mentioned that I know the default values are stored in
> pg_attrdef, in 'human' mode at adsrc, and in 'pg' mode at adbin; I could
> look there, but don't know how to 'evaluate' these to the real-world
> equivalents (ie, instead of finding 'f' or FALSE in a column, I get
> 'f'::bool, and by the time this gets to the GUI app and back again, it
> looks like '''f''::bool' to PG, so it sees it as text, not as the boolean
> value false. Similarly, "current_user"() isn't resolved, etc.
>
> So, one solution might be: is there a way to 'resolve' these before they
> come to the front-end?

Thanks to some help, I have a (working but hackish) solution:

1) I'll need to be able to turn bools into text. So:

CREATE FUNCTION text(bool) RETURNS text AS
'SELECT CASE WHEN TRUE THEN -1 ELSE 0 END;'
LANGUAGE 'sql' WITH (ISCACHABLE);

[ I'm building a front-end in Access, where the numeric
representation for truth=-1 and false=0, hence the values
here. ]

2) I need a way to 'evaluate' a PostgreSQL expression, such as
''foo'':text, 'f'::bool, "current_user"(), etc.

CREATE FUNCTION eval(text) RETURNS text AS '
DECLARE
r record;
q text;
BEGIN
q := ''SELECT ('' || $1 || ')::text AS a'';
FOR r IN EXECUTE q
LOOP
RETURN r.a
END LOOP;
END;
' LANGUAGE 'plpgsql';

3) I need a query to find the human-readable expression for each
column, and show me the evaluated version:

SELECT a.attname,
eval(d.adsrc)
FROM pg_class c,
pg_attribute a,
pg_attrdef d
WHERE c.oid = d.adrelid
AND d.adnum = a.attnum
AND a.attrelid = c.oid
AND c.relname = 'your_table_name'
ORDER BY a.attnum;

Seems to work.

--
Joel Burton <jburton(at)scw(dot)org>
Director of Information Systems, Support Center of Washington