Обсуждение: How to access NEW or OLD field given only the field's name?

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
manyinstances of this validation, I want to write a single procedure and create multiple triggers, each with a
differentset 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. 

I am specifically targeting PostgreSQL 9.1.

Thanks for any hints!
François Beausoleil

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
havemany instances of this validation, I want to write a single procedure and create multiple triggers, each with a
differentset 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.klaver@aklaver.com

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?

Yes see ^^^ below.
>
>> 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
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 03/19/2014 08: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
havemany instances of this validation, I want to write a single procedure and create multiple triggers, each with a
differentset 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. 
>
> I am specifically targeting PostgreSQL 9.1.
>
> Thanks for any hints!
>

You can do that easily with the hstore extension like so:

x := (hstore(new)->tg_argv[0])::bigint;
y := (hstore(new)->tg_argv[1])::bigint[];

--
Vik

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