Обсуждение: 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.