From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | François Beausoleil <francois(at)teksol(dot)info> |
Cc: | Forums postgresql <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: How to access NEW or OLD field given only the field's name? |
Date: | 2014-03-19 23:48:53 |
Message-ID: | 532A2CE5.4050208@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 03/19/2014 02:01 PM, François Beausoleil wrote:
> Hi,
>
> Le 2014-03-19 à 16:19, Adrian Klaver a écrit :
>
>> On 03/19/2014 12:48 PM, François Beausoleil wrote:
>>>
>>> Cross-posted from
>>> https://dba.stackexchange.com/questions/61271/how-to-access-new-or-old-field-given-only-the-fields-name
>>>
>>
>>
>> Well two things:
>>
>> 1) From the above link:
>> Note that parameter symbols can only be used for data values — if you
>> want to use dynamically determined table or column names, you must
>> insert them into the command string textually. For example, if the
>> preceding query needed to be done against a dynamically selected
>> table, you could do this:
>
> Is there an example missing here?
>
>> So:
>>
>> Instead of 'SELECT $1 '.. use 'SELECT ' || TG_ARGV[0] || ..
>>
>> 2) Use NEW outside the quotes.
>>
>> So:
>> 'FROM ' NEW.*
>
> That doesn't seem to work?
>
> CREATE OR REPLACE FUNCTION validate_arrays_sum_equals_total() RETURNS
> TRIGGER AS $$
> DECLARE
> total bigint;
> array_sum bigint;
> BEGIN
> EXECUTE 'SELECT $1 FROM ' NEW.* INTO total USING TG_ARGV[0];
> RAISE EXCEPTION 'Total: %, social_impressions: %', total,
> NEW.social_impressions;
> RETURN NEW;
> END
> $$ LANGUAGE plpgsql;
>
> psql:db/functions.sql:117: ERROR: syntax error at or near "."
> LINE 6: EXECUTE 'SELECT $1 FROM ' NEW.* INTO total USING TG_ARGV[0...
Some experimenting showed that NEW.* does not work. So plan B:
EXECUTE 'SELECT $1.' || TG_ARGV[0] || INTO total USING NEW;
RAISE EXCEPTION 'Total: %, social_impressions: %', total,
> ^
> Thanks,
> François
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2014-03-20 00:07:28 | Re: Alter column with views depended on it without drop views |
Previous Message | Vik Fearing | 2014-03-19 21:59:21 | Re: How to access NEW or OLD field given only the field's name? |