Search Postgresql Archives

Re: dynamic plpgsql question

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

 




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"

- Marc


[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