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

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

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

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

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

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 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.

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

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

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

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

Le 2014-03-19 à 19:48, Adrian Klaver a écrit :

On 03/19/2014 02:01 PM, François Beausoleil wrote:

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,

Thank you, Adrian. I've accepted the following answer on StackExchange: http://dba.stackexchange.com/a/61304/3935

It's your solution, but with the extra call to format, preventing SQL injection attacks.

Thanks!
François