Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Cross posted to the PostgreSQL general mailing list postgresql.org/message-id/… –  François Beausoleil Mar 19 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 at 0:22
    
@CraigRinger: Well, no. You are too pessimistic today. There is a way with dynamic SQL. –  Erwin Brandstetter Mar 20 at 2:50

1 Answer 1

up vote 7 down vote accepted

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_demo()
  RETURNS TRIGGER AS
$func$
DECLARE
  test text;
BEGIN

EXECUTE format('SELECT ($1).%I::text', TG_ARGV[0])
USING NEW
INTO  test;

-- do something with test ...

RAISE NOTICE 'It works. The value of NEW.% is >>%<<.', TG_ARGV[0], test;

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 with it without casting ...

Using format() with %I to safeguard against SQL injection.

SQL Fiddle. Raising an exception to see the proof.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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