Accessing fields of records in triggers by column name passed as trigger arg

Question

I have a set of tables which make use of a discriminator column to make distinctions about entity type. Certain of those tables describe either relationships between defined groups of entities or contain more information for a particular type of entity. I want to have integrity checks on the data in the table. Originally these were written as checks but postgres does not handle table ordering in backups well so I thought to rewrite the checks as triggers so that I can turn off the triggers when restoring the database.

Since the actual column name of entity will change from table to table and I would like to do it in a DRY fashion, I tried to write a single trigger function that took the id of the entity as an argument and verified that it was of the correct type.

I am trying to call a function trigger that takes an argument

c.is_earth_based_measurement_tg(bigint).

The actual trigger is written as follows:

CREATE TRIGGER earth_based_measurement_locations_tg1
  BEFORE INSERT OR UPDATE
  ON measurements.earth_based_measurement_locations
  FOR EACH ROW
    EXECUTE PROCEDURE c.is_earth_based_measurement_tg(NEW.fk_measurement);

when I try to save the trigger, I get 'ERROR: syntax error at or near "."' referring to NEW.fk_measurement. What is the proper way to write this trigger?

Thanks.

Answer 1

You don't need to pass NEW.anything as an argument; it's automatically supplied to the trigger function as the variable NEW. So your trigger can just access NEW.fk_measurement from anywhere within it, so long as tg_op is INSERT or UPDATE (NEW is not set for DELETE or TRUNCATE triggers).

Triggers can take arguments, but they appear (as zero323 notes) in TG_ARGV. They don't need to be used for column values, and in fact cannot reference column values; their purpose is for things like parameterized triggers, triggers with optional functionality, etc.

On re-reading your question you do need a parameter for the column name of interest. Unfortunately it's very awkward to access columns by dynamic name in the NEW tuple.

You'd create the trigger as:

CREATE TRIGGER earth_based_measurement_locations_tg1
  BEFORE INSERT OR UPDATE
  ON measurements.earth_based_measurement_locations
  FOR EACH ROW
    EXECUTE PROCEDURE c.is_earth_based_measurement_tg('fk_measurement');

i.e. passing the colname as a string literal.

The trigger procedure would be defined as:

CREATE OR REPLACE FUNCTION is_earth_based_measurement_tg() RETURNS trigger AS $$
BEGIN
  IF tg_op = 'INSERT' OR tg_op = 'UPDATE' THEN
    PERFORM my_func(hstore(NEW) -> TG_ARGV[0]);
  END IF;
END;
$$ LANGUAGE plpgsql;

... or whatever else you want to do with the value you extract from the column.

You cannot set the value of the column in NEW via a dynamic column name in current PostgreSQL versions (up to and including 9.4).

Note that we convert the NEW tuple into a hstore, then de-reference it by column name.

Answer 2

Trigger functions cannot be declared with arguments. OLD, NEW and several trigger related variables are passed be default using so called TG_ARGV and can be accessed inside trigger functions without additional steps. You check official documentation for details but generally it should look similar to this:

CREATE OR REPLACE FUNCTION is_earth_based_measurement_tg()
RETURNS TRIGGER AS $earth_based_measurement_locations_tg1$
    BEGIN
       IF (NEW.fk_measurement = 'foo') THEN
           -- Do something
       END IF;
    END;
$earth_based_measurement_locations_tg1$ LANGUAGE plpgsql;