Postgresql trigger function with parameters

Question

I want to create a trigger on a table called takes in postgresql to update a value in another table called student I'm trying to do it in the following way. But I'm getting an error that there is syntax error near "OLD". I don't understand whats wrong with this. This is my code:

CREATE OR REPLACE FUNCTION upd8_cred_func
      (id1 VARCHAR, gr1 VARCHAR,id2 VARCHAR, gr2 VARCHAR) 
      RETURNS void AS $$
 BEGIN
    IF  (id1=id2 and gr1 is null and gr2 is not null) THEN 
        update student set tot_cred = tot_cred + 6 where id = id1;
    END IF;
    RETURN;
 END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER upd8_cred
    AFTER UPDATE ON takes
    FOR EACH ROW
    EXECUTE PROCEDURE upd8_cred_func(OLD.id,OLD.grade,NEW.id,NEW.grade);

Solution 1

You do not need to pass the NEW and OLD as parameters to the trigger function. They are automagically available there:

http://www.postgresql.org/docs/9.1/interactive/trigger-definition.html :

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.)

About the records passed to the trigger procedure, please see http://www.postgresql.org/docs/9.1/interactive/plpgsql-trigger.html :

When a PL/pgSQL function is called as a trigger, several special variables are created automatically in the top-level block. They are: [...] NEW, [...] OLD [...]

As SeldomNeedy pointed in the comment below, you can still pass and use parameters to the trigger function. You declare the function as taking no parameters, but when defining the trigger (by CREATE TRIGGER), you may add some.

They will be available for the trigger as TG_NARG (the number of such parameters), and TG_ARGV[] (an array of text values).

Solution 2

As Greg stated, trigger functions can take arguments, but the functions themselves cannot have declared parameters. Here's a simple example in plpgsql:

CREATE TABLE my_table ( ID SERIAL PRIMARY KEY ); -- onelined for compactness

CREATE OR REPLACE FUNCTION raise_a_notice() RETURNS TRIGGER AS
$$
DECLARE
    arg TEXT;
BEGIN
    FOREACH arg IN ARRAY TG_ARGV LOOP
        RAISE NOTICE 'Why would you pass in ''%''?',arg;
    END LOOP;
    RETURN NEW; -- in plpgsql you must return OLD, NEW, or another record of table's type
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER no_inserts_without_notices BEFORE INSERT ON my_table
FOR EACH ROW EXECUTE PROCEDURE raise_a_notice('spoiled fish','stunned parrots');

INSERT INTO my_table DEFAULT VALUES;

-- the above kicks out the following:
--
-- NOTICE:  Why would you pass in 'spoiled fish'?
-- NOTICE:  Why would you pass in 'stunned parrots'?
--

There are a few other goodies such as TG_NARGS (to know how many args you got without looping through them) discussed in the docs. There's also information there about how to get the name of the triggering table in case you have mostly-but-not-quite-shared logic for one trigger-function that spans a number of tables.

Solution 3

The trigger function can have parameters, but, you can't have those parameters passed like a normal function (e.g. arguments in the function definition). You can get the same result... In python you get access to the OLD and NEW data as the answer above describes. For example, I can use TD['new']['column_name'] in python to reference the new data for column_name. You also have access to the special variable TD['args']. So, if you like:

create function te() returns trigger language plpython2u as $function$
    plpy.log("argument passed 1:%s 2:%s" %(TD['args'][0], TD['args'][1], ))
$function$

create constraint trigger ta after update of ttable
for each for execute procedure te('myarg1','myarg2');

Granted, these arguments are static, but, they are useful when calling a common trigger function from multiple trigger declarations. I am pretty sure that the same variables are available for other stored procedure languages. (sorry if the code doesn't work verbatim, but, I do practice this technique, so I know you can pass arguments!).

Comments

[Seldom 'Where's Monica' Needy]

This answer (arguably also the spec for this) is wrong with regard to plpgsql trigger-functions not being able to accept arguments. They must simply be defined as "taking no parameters." Arguments passed to trigger functions can be accessed using the TG_ARGV array (which has indexes starting at 0). This is specified in the the Trigger Procedures documentation.

[Seldom 'Where's Monica' Needy]

I've gone ahead and posted an answer that has a demo for the native plpgsql scripting language, if you'd like to have a look.

[Seldom 'Where's Monica' Needy]

Thanks for the edit. Downvote removed since misinformation was corrected. C:

[Krauss]

"they are useful when calling a common trigger function from multiple trigger declarations" - This might be the only logical reason to do it. I cannot find any other case in which passing a parameter to a trigger function could be useful.