From: | Alban Hertroys <alban(at)magproductions(dot)nl> |
---|---|
To: | Marc Evans <Marc(at)SoftwareHackery(dot)Com> |
Cc: | Erik Jones <erik(at)myemma(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: dynamic plpgsql question |
Date: | 2006-12-14 09:16:01 |
Message-ID: | 45811651.2020006@magproductions.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Marc Evans wrote:
>>> 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"
Writing an SP that fetches an array or a text string with column names
given a table name as input is not that hard to write.
Simply fetch "SELECT column_name FROM information_schema.columns WHERE
schema_name = $1 AND table_name = $2" into an array and use
array_to_string() or some such to make it a comma separated string of
columns. You can use that string in your dynamic query.
Cheers.
--
Alban Hertroys
alban(at)magproductions(dot)nl
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
// Integrate Your World //
From | Date | Subject | |
---|---|---|---|
Next Message | Alban Hertroys | 2006-12-14 09:19:59 | Re: MySQL drops support for most distributions |
Previous Message | Alban Hertroys | 2006-12-14 09:05:52 | Re: A VIEW mimicing a TABLE |