8

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 Executing Dynamic Commands 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.

  • Cross posted to the PostgreSQL general mailing list postgresql.org/message-id/… – François Beausoleil Mar 19 '14 at 21:05
  • AFAIK the only way to dynamically access fields within NEW at the moment is to use hstore(NEW) and then access the fields as hstore values keyed by column name. Which sucks, because then they're all cast to text and if you want to work with them in their original type you have to cast them back. Alternately, you can write a trigger in another procedural language like PL/Python that has better support for dynamic record access. – Craig Ringer Mar 20 '14 at 0:22
  • @CraigRinger: Well, no. You are too pessimistic today. There is a way with dynamic SQL. – Erwin Brandstetter Mar 20 '14 at 2:50
14

Actually, since NEW is a well defined composite type, you can just access any column with plain and simple attribute notation. SQL itself does not allow dynamic identifiers (table or column names etc.). But you can use dynamic SQL with EXECUTE in a PL/pgSQL function.

Demo

CREATE OR REPLACE FUNCTION trg_demo1()
  RETURNS TRIGGER AS
$func$
DECLARE
   _col_value text;
   _col_name  text := quote_ident(TG_ARGV[0]);  -- escape identifier
BEGIN
   EXECUTE format('SELECT ($1).%s::text', _col_name)
   USING NEW
   INTO  _col_value;

   -- do something with _col_value ...

   RAISE NOTICE 'It works. The value of NEW.% is >>%<<.', _col_name, _col_value;

   RETURN NEW;
END
$func$ LANGUAGE plpgsql;

The cast to text is optional. Using it, because it works universally. If you know the type, you can work without casting ...

Using format() with %s, because the identifier is already escaped at that point.
Else, use format() with %I to safeguard against SQL injection.

Alternatively, in Postgres 9.3 or later, you can convert NEW to JSON with to_json() and access columns as keys:

CREATE OR REPLACE FUNCTION trg_demo2()
  RETURNS TRIGGER AS
$func$
DECLARE
   _col_value text := to_json(NEW) ->> TG_ARGV[0];  -- no need to escape identifier
BEGIN
   RAISE NOTICE 'It works. The value of NEW.% is >>%<<.', TG_ARGV[0], _col_value;
   RETURN NEW;
END
$func$ LANGUAGE plpgsql;

Since the column name is not concatenated into an SQL string, SQL injection is not possible, and the name does not need to be escaped.

db<>fiddle here (with EXCEPTION instead of NOTICE to make the effect visible).

Related:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.