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.

How can I access table field on creating a trigger?

CREATE TRIGGER foo_trigger
AFTER INSERT OR UPDATE OR DELETE ON foo
FOR EACH ROW EXECUTE PROCEDURE proc(foo.field);//here goes the parameter
share|improve this question

1 Answer 1

Per documentation:

The trigger function must be defined before the trigger itself can be created. The trigger function must be declared as a function taking no arguments and returning type trigger. (The trigger function receives its input through a specially-passed TriggerData structure, not in the form of ordinary function arguments.)

Bold emphasis mine.

To pass those parameters:

arguments

An optional comma-separated list of arguments to be provided to the function when the trigger is executed. The arguments are literal string constants. Simple names and numeric constants can be written here, too, but they will all be converted to strings. Please check the description of the implementation language of the trigger function to find out how these arguments can be accessed within the function; it might be different from normal function arguments.

And finally, in the chapter Trigger Procedures:

Note that the function must be declared with no arguments even if it expects to receive arguments specified in CREATE TRIGGER — trigger arguments are passed via TG_ARGV, as described below.

...

TG_ARGV[]

Data type array of text; the arguments from the CREATE TRIGGER statement. The index counts from 0. Invalid indexes (less than 0 or greater than or equal to tg_nargs) result in a null value.

The final difficulty is that SQL only works with static identifiers. If you want to parametrize a column name, you need dynamic SQL. In a plpgsql function, use EXECUTE for that.

Example

CREATE OR REPLACE FUNCTION trg_foo()
  RETURNS trigger AS
$func$
DECLARE
   _result text;
BEGIN
   CASE TG_OP
   WHEN 'INSERT', 'UPDATE' THEN
      EXECUTE format('SELECT %I FROM %s WHERE tbl_id = $1', TG_ARGV[1]
                                                          , TG_ARGV[0]::regclass)
      INTO  _result
      USING NEW.sometbl_id;
   WHEN ...

   END CASE;

   -- do something with _result ...

   RETURN NULL;   -- since it's an AFTER trigger
END
$func$  LANGUAGE plpgsql;


CREATE TRIGGER foo_trigger
AFTER INSERT OR UPDATE OR DELETE ON foo
FOR EACH ROW
EXECUTE PROCEDURE trg_foo('foo', 'field'); -- two params: table, field

Answer on SO with details about the cast to regclass:
Table name as a PostgreSQL function parameter

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.