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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
