Re: How to access NEW or OLD field given only the field's name?

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