From: | Marc Evans <Marc(at)SoftwareHackery(dot)Com> |
---|---|
To: | Erik Jones <erik(at)myemma(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: dynamic plpgsql question |
Date: | 2006-12-13 16:12:59 |
Message-ID: | 20061213111122.Q4899@me.softwarehackery.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Filip Rembiałkowski | 2006-12-13 16:17:36 | Re: error messages without schema name |
Previous Message | Tom Lane | 2006-12-13 16:12:46 | Re: Online index builds |