Search Postgresql Archives

Re: dynamic plpgsql question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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...

--
erik jones <erik@xxxxxxxxxx>
software development
emma(r)



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux