Postgresql – How to send a parameter to a trigger function from an external resource

plpgsqlpostgresqltrigger

I have a trigger that executes before delete to audit changes in tables. The problem I have is that one of the fields I save in the xtable_audit table is the user id (of an user table) that is used in the web application. How can I send that external iduser from the web application to the trigger function when a delete occurs in any table that has that trigger?

Best Answer

You could use the set_config() and current_setting() functions for that. In the application just after establishing the database connection you call something like

select set_config('myapp.app_user','johnny',false);

to set a custom configuration variable for the session. In the trigger you then use

select into v_actualuser current_setting('myapp.app_user');

to retrieve the value.

This blog post even has a sample trigger for your viewing pleasure.

Related Solutions

Postgresql – Call multiple functions from trigger

A trigger can only ever call one tigger function, so no to item 1.

The preferable form is item 2. IMO. You can put as many SQL statements into a single plpgsql function as you want.

Item 3. is possible, too. Well, not exactly the same trigger, the name would have to be different. Triggers on the same event fire in alphabetical order, btw. But I see no gain in two separate functions. Just more code and overhead and two function invocations, which is more expensive.

2. is the undisputed victor.

Postgresql – Trigger function check if all NEW.* fields exist in another table

Your attempt fails for multiple reasons. First of all a row is not an array. This construct is just not possible:

FOR field IN NEW LOOP ...

But there is more.

I am not sure I like the general idea. This kind of trigger would run for at least every statement (don't use a row-level trigger for this!), which is quite a bit of overhead. It is also error-prone to put DDL commands in a trigger. Especially if you are a beginner.

That said, here is a proof of concept:

Trigger function:

CREATE OR REPLACE FUNCTION trigger_hv()
  RETURNS trigger AS
$func$
DECLARE
    _sql text;
BEGIN
   SELECT INTO _sql
         'ALTER TABLE ' || quote_ident(TG_RELNAME || '_hv') || ' ADD COLUMN '
       || string_agg(quote_ident(attname) || ' ' || att_type, ', ADD COLUMN ')
   FROM  (
      SELECT attname, format_type(atttypid, atttypmod) AS att_type
      FROM   pg_attribute
      WHERE  attrelid = TG_TABLE_NAME::regclass
      AND    NOT attisdropped   -- no dropped (dead) columns
      AND    attnum > 0         -- no system columns
      ) a
   LEFT   JOIN (
      SELECT attname
      FROM   pg_attribute
      WHERE  attrelid = (TG_TABLE_NAME || '_hv')::regclass
      AND    NOT attisdropped
      AND    attnum > 0
      ) b USING (attname)  -- ignoring data type!
   WHERE  b.attname IS NULL;

   IF _sql IS NOT NULL THEN
      EXECUTE _sql;
   END IF;

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

Trigger:

CREATE TRIGGER trg_foo_hv
BEFORE INSERT ON foo
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_hv();

SQL Fiddle.

This is a basic proof of concept. It does not check for matching data types and ignores things like schema search_path, NOT NULL constraints or COLLATION.

Related (with more explanation and links):

Best Answer

Related Solutions

Postgresql – Call multiple functions from trigger

Postgresql – Trigger function check if all NEW.* fields exist in another table

Related Question