Access table field inside CREATE TRIGGER statement

Question

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

Answer 1

The manual on how to create a trigger function:

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.

The manual on how to pass 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. Use EXECUTE in a PL/pgSQL function.

Example

CREATE OR REPLACE FUNCTION trg_foo()
  RETURNS trigger
  LANGUAGE plpgsql 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$;
CREATE TRIGGER foo_trigger
AFTER INSERT OR UPDATE OR DELETE ON foo
FOR EACH ROW
EXECUTE FUNCTION trg_foo('foo', 'field'); -- two params: table, field

Outdated versions of Postgres still require the keyword PROCEDURE instead of FUNCTION. See:

• Trigger uses a procedure or a function?

Schema-qualify the table name to avoid ambiguity. Like 'public.foo'.
Details for the cast to regclass:

• Table name as a PostgreSQL function parameter