Re: dynamic field names in a function.

On Thu, Mar 29, 2001 at 02:38:31PM -0800, Soma Interesting wrote:
>
> I want to be able to reference NEW.field_0 though NEW.field_x where x is
> coming from NEW.qty in a FOR loop of pl/pgsql function. Is this possible?
>
> In other words:
>
> FOR j IN 0..NEW.str LOOP
>
>     ans := ''q'' || i || ''a'' || j;
>     cor := ''q'' || i || ''c'' || j;
>     eval := 'q'' || i || ''e'' || j;
>
>     IF NEW.ans = NEW.cor
>     THEN NEW.eval := 1;
>     END IF;
>
> END LOOP;

I think maybe querying system catalogs might help your approach.  I'm
not entirely clear on what you're trying to do, but you can get the name
of the relation that caused the trigger to fire (TG_RELNAME).  Then
query the pg_class table for the "oid" of the class where relnam =
TG_RELNAME, join with pg_attribute on pg_class.oid =
pg_attribute.attrelid and pg_attribute.attnum > 0 (to skip internal
system fields).  Then you have a set of records containing all of the
field names for the relation which you can compare to the concatenation
of your "field" and NEW.qty.  Hope this is making some sense.  Here's a
quick example query on a known relation called "units".

select pg_attribute.* from pg_attribute, pg_class
where pg_attribute.attrelid = pg_class.oid
and pg_class.relname = 'units'
and pg_attribute.attnum > 0;

You'll probably be most interested in "pg_attribute.attname".
--
Eric G. Miller <egm2@jps.net>