Skip site navigation (1) Skip section navigation (2)

Re: dynamic field names in a function.

From: "Eric G(dot) Miller" <egm2(at)jps(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: dynamic field names in a function.
Date: 2001-03-31 03:29:56
Message-ID: 20010330192956.E29151@calico.local (view raw, whole thread or download thread mbox)
Lists: pgsql-general
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;

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(at)jps(dot)net>

In response to

pgsql-general by date

Next:From: Tom LaneDate: 2001-03-31 04:56:26
Subject: Re: Consistent pg_dump's
Previous:From: Eric G. MillerDate: 2001-03-31 02:37:40
Subject: Re: Re: function to operate on same fields, different records?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group