Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxx


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux