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.klaver@aklaver.com