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

[François Beausoleil]

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...
                                       ^
Thanks,
François

smime.p7s
Description: S/MIME cryptographic signature