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

On 03/19/2014 12:48 PM, François Beausoleil wrote:
> Hi all!
>
> Cross-posted from https://dba.stackexchange.com/questions/61271/how-to-access-new-or-old-field-given-only-the-fields-name
>
> I'm writing a validation trigger. The trigger must validate that the sum of an array equals another field. Since I have many instances of this validation, I want to write a single procedure and create multiple triggers, each with a different set of fields to check.
>
> For example, I have the following schema:
>
> CREATE TABLE daily_reports(
> start_on date
> , show_id uuid
> , primary key(start_on, show_id)
>
> -- _graph are hourly values, while _count is total for the report
> , impressions_count bigint not null
> , impressions_graph bigint[] not null
>
> -- interactions_count, interactions_graph
> -- twitter_interactions_count, twitter_interactions_graph
> );
>
> The validation must confirm that impressions_count = sum(impressions_graph).
>
> I'm stuck because I don't know how to dynamically access a field from NEW from within plpgsql:
>
> CREATE FUNCTION validate_sum_of_array_equals_other() RETURNS TRIGGER AS $$
> DECLARE
> total bigint;
> array_sum bigint;
> BEGIN
> -- TG_NARGS = 2
> -- TG_ARGV[0] = 'impressions_count'
> -- TG_ARGV[1] = 'impressions_graph'
>
> -- How to access impressions_count and impressions_graph from NEW?
>
> RETURN NEW;
> END
> $$ LANGUAGE plpgsql;
>
> CREATE TRIGGER validate_daily_reports_impressions
> ON daily_reports BEFORE INSERT OR UPDATE
> FOR EACH ROW EXECUTE
> validate_sum_of_array_equals_other('impressions_count', 'impressions_graph');
>
> I tried http://www.postgresql.org/docs/9.1/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN by doing EXECUTE 'SELECT $1 FROM NEW' INTO total USING TG_ARGV[0], but PL/PGsql complains that NEW is an unknown relation.

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:

So:

Instead of 'SELECT $1 '.. use 'SELECT ' || TG_ARGV[0] || ..

2) Use NEW outside the quotes.

So:
'FROM ' NEW.*

>
> I am specifically targeting PostgreSQL 9.1.
>
> Thanks for any hints!
> François Beausoleil
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com