Re: dynamic plpgsql question - Mailing list pgsql-general
From | Erik Jones |
---|---|
Subject | Re: dynamic plpgsql question |
Date | |
Msg-id | 45803FFE.1000508@myemma.com Whole thread Raw |
In response to | Re: dynamic plpgsql question (Marc Evans <Marc@SoftwareHackery.Com>) |
List | pgsql-general |
Marc Evans wrote: > > On Wed, 13 Dec 2006, Erik Jones wrote: > >> Marc Evans wrote: >>> >>> On Wed, 13 Dec 2006, Erik Jones wrote: >>> >>>> Marc Evans wrote: >>>>> Hi - >>>>> >>>>> I am struggling with a trigger function in plpgsql, and am hoping >>>>> that someone on this list can't show me a way to do what I need. >>>>> >>>>> In the trigger, TG_ARGV[0] is the name of a column that I want to >>>>> evaluate. This code shows the concept, though is not functional: >>>>> >>>>> CREATE OR REPLACE FUNCTION foo() RETURNS TRIGGER AS $$ >>>>> DECLARE >>>>> column_name TEXT := TG_ARGV[0]; >>>>> data TEXT; >>>>> BEGIN >>>>> EXECUTE 'SELECT NEW.' || column_name INTO data; >>>>> -- ... >>>>> END; >>>>> $$ LANGUAGE plpgsql; >>>>> >>>>> When I try to use that code, I receive: >>>>> >>>>> c3i=> insert into test_table values (1,1); >>>>> ERROR: NEW used in query that is not in a rule >>>>> CONTEXT: SQL statement "SELECT NEW.magic" >>>>> >>>>> How can I get the value of NEW.{column_name} (aka NEW.magic in >>>>> this specific test case) into the variable data? >>>> EXECUTE 'SELECT ' || NEW.column_name ';' INTO data; >>> >>> Thanks for the suggestion. Unfortunately, it does not work: >>> >>> CREATE OR REPLACE FUNCTION foo() RETURNS TRIGGER AS $$ >>> DECLARE >>> column_name TEXT := TG_ARGV[0]; >>> data TEXT; >>> BEGIN >>> EXECUTE 'SELECT ' || NEW.column_name || ';' INTO date; >>> -- ... >>> END; >>> $$ LANGUAGE plpgsql; >>> >>> c3i=> insert into test_table values (1,1); >>> ERROR: record "new" has no field "column_name" >> Ah, sorry, I'd just arrived at work and wasn't quite away as of yet. >> AFAIK, plpgsql doesn't have any facilities for variable substitution >> in variable names (called variable variables in some languages). >> However, if plpgsql is your only procedural option (plperl, I've >> heard, does support this feature) and the possible values for column >> name are known to you, there is a hackish workaround: >> >> IF(column_name = 'foo') THEN >> EXECUTE 'SELECT ' || NEW.foo || ';' INTO data; >> ELSIF(column_name = 'bar') THEN >> EXECUTE 'SELECT ' || NEW.bar || ';' INTO data; >> ELSIF >> . >> . >> . >> >> You get the picture... > > Thanks for the suggestion. I would be quiet content to use plperl, if > I could figure out a way to do the equivilant of plpgsql's: > > EXECUTE 'INSERT INTO ' || table_name || ' VALUES(NEW.*)'; > > I suppsoe that in plperl I could walk the list of keys in $_TD->{new} > building a list of columns and values that are then placed in a > spi_prepare. Would that be the recommended technique? > > - Marc Sure, that'll work. Although, I'll admit, that with plperl I don't have much experience so, if there's a better way of doing that, someone else might know. Also, for a straight insert like that I don't really see the need for using spi_prepare. Just feed the INSERT query string to spi_exec_query. -- erik jones <erik@myemma.com> software development emma(r)
pgsql-general by date: