From: | François Beausoleil <francois(at)teksol(dot)info> |
---|---|
To: | Forums postgresql <pgsql-general(at)postgresql(dot)org> |
Subject: | How to access NEW or OLD field given only the field's name? |
Date: | 2014-03-19 19:48:09 |
Message-ID: | 848750FE-64BA-4F1C-A582-740E451CF84C@teksol.info |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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!
François Beausoleil
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2014-03-19 20:19:00 | Re: How to access NEW or OLD field given only the field's name? |
Previous Message | Shaun Duncan | 2014-03-19 17:12:29 | Hot standby read slaves exceed max delay on WAL segment. Replication lag. |