Thread: Variable column name in plpgsql function

I am trying to create a trigger that needs to capture the primary key
value out of NEW variable.

However the trigger can be called from 2 different tables whose PKs are

Table1id
Table2id

Is possible to declare a variable to build the column name

Tableid varchar(20) := TG_RELNAME || ''id'';

And then use this variable to get the PK value?

Tableidvalue int4 := NEW.tableid;


Thanks,
Woody

----------------------------------------
iGLASS Networks
211-A S. Salem St
Apex NC 27502
(919) 387-3550 x813
www.iglass.net

George Woodring wrote:
>
> Is possible to declare a variable to build the column name
>
> Tableid varchar(20) := TG_RELNAME || ''id'';
>
> And then use this variable to get the PK value?
>
> Tableidvalue int4 := NEW.tableid;

No. You can build a dynamic query via EXECUTE, but that can't access
NEW/OLD.

--
   Richard Huxton
   Archonet Ltd

Richard Huxton wrote:

> George Woodring wrote:
>
>>
>> Is possible to declare a variable to build the column name
>>
>> Tableid varchar(20) := TG_RELNAME || ''id'';
>>
>> And then use this variable to get the PK value?
>>
>> Tableidvalue int4 := NEW.tableid;
>
>
> No. You can build a dynamic query via EXECUTE, but that can't access
> NEW/OLD.
>
If there's only the two possibilities, you could just use something like
IF TG_RELNAME = 'table1' THEN
    id := NEW.table1id;
ELSE
    id := NEW.table2id;
END IF;

But dynamically referring to NEW.variable doesn't work in plpgsql.
Apparently you can do this in some of the other pl languages though
(plperl for example).

-Mark.