Postgresql – Access table field on creating a trigger postgresql

plpgsqlpostgresqlstored-procedurestrigger

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

Best Answer

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

Related Solutions

Postgresql – Scaling PostgreSQL TRIGGER(s)

Basically we would like to create a TRIGGER for each table we want to be notified for an UPDATE/INSERT/DELETE operation. Once this trigger fires it will execute a function that will simply append a new row (encoding the event) to a log table that we will then poll from an external service.

That's a pretty standard use for a trigger.

Before going all in with Postgres TRIGGER(s) we would like to know how they scale: how many triggers can we create on a single Postgres installation?

If you keep creating them, eventually you'll run out of disk space.

There's no specific limit for triggers.

PostgreSQL limits are documented on the about page.

Does they affect query performance?

It depends on the trigger type, trigger language, and what the trigger does.

A simple PL/PgSQL BEFORE ... FOR EACH STATEMENT trigger that doesn't do anything has near-zero overhead.

FOR EACH ROW triggers have higher overhead than FOR EACH STATEMENT triggers. Scaling, obviously, with the affected row counts.

AFTER triggers are more expensive than BEFORE triggers because they must be queued up until the statement finishes doing its work, then executed. They aren't spilled to disk if the queue gets big (at least in 9.4 and below, may change in future) so huge AFTER trigger queues can cause available memory to overrun, resulting in the statement aborting.

A trigger that modifies the NEW row before insert/update is cheaper than a trigger that does DML.

The specific use case you want would perform better with an in-progress enhancement that might make it into PostgreSQL 9.5 (if we're lucky), where FOR EACH STATEMENT triggers can see virtual OLD and NEW tables. This isn't possible in current PostgreSQL versions, so you must use FOR EACH ROW triggers instead.

Did anyone before tried this ?

Of course. It's a pretty standard use for triggers, along with auditing, sanity checking, etc.

You'll want to look into LISTEN and NOTIFY for a good way to wake up your worker when changes to the task table happen.

You're already doing the most important thing by avoiding talking to external systems directly from triggers. That tends to be problematic for performance and reliability. People often try to do things like send mail directly from a trigger, and that's bad news.

PostgreSQL Triggers – How to Prevent a Trigger from Being Fired by Another Trigger

^{(Obvious error in the trigger logic aside.)}
In Postgres 9.2 or later, use the function pg_trigger_depth() that @Akash already mentioned in a condition on the trigger itself (instead of the body of the trigger function), so that the trigger function is not even executed when called from another trigger (including itself - so also preventing loops).
This typically performs better and is simpler and cleaner:

CREATE TRIGGER set_history
BEFORE UPDATE ON field_data
FOR EACH ROW 
WHEN (pg_trigger_depth() < 1)
EXECUTE PROCEDURE gener_history();

The expression pg_trigger_depth() < 1 is evaluated before the trigger function is entered. So it evaluates to 0 in the first call. When called from another trigger, the value is higher and the trigger function is not executed.

Best Answer

Example

Related Solutions

Postgresql – Scaling PostgreSQL TRIGGER(s)

PostgreSQL Triggers – How to Prevent a Trigger from Being Fired by Another Trigger

Related Question