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@scw.org>
Director of Information Systems, Support Center of Washington