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

Поиск
Список
Период
Сортировка
От François Beausoleil
Тема How to access NEW or OLD field given only the field's name?
Дата
Msg-id 848750FE-64BA-4F1C-A582-740E451CF84C@teksol.info
обсуждение исходный текст
Ответы Re: How to access NEW or OLD field given only the field's name?  (Adrian Klaver <adrian.klaver@aklaver.com>)
Re: How to access NEW or OLD field given only the field's name?  (Vik Fearing <vik.fearing@dalibo.com>)
Список 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
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


Вложения

В списке pgsql-general по дате отправления:

Предыдущее
От: Shaun Duncan
Дата:
Сообщение: Hot standby read slaves exceed max delay on WAL segment. Replication lag.
Следующее
От: Adrian Klaver
Дата:
Сообщение: Re: How to access NEW or OLD field given only the field's name?