Обсуждение: passing parameters to a trigger function

I can't figure out how to pass parameters to a trigger function. 

I checked the documentation and saw that trigger functions don't take params in the usual fashion, 
but couldn't find an example of a pl-sql trigger function that used the original row data within the function.


What I want is an on update trigger that creates an entry in a second table.  The second (history)  table has a subset of the columns in the first.

Here's what I have so far: 

-- THIS IS WRONG - CANT PASS PARAMS INTO TRIGGER FUNCTION


CREATE OR REPLACE FUNCTION audit_task ("param type declarations were here") RETURNS TRIGGER AS '	  
	-- create an audit trail record
BEGIN
	-- Perform the insert  
	
	INSERT INTO TASK_h  (id, 

		         updated_by, 
			 updated, 
			 name, 
			 description 
			 ) 
		VALUES ($1, $2, $3, $4, $5);
	
	RETURN NULL;		 
END;

' LANGUAGE plpgsql;


-- THE TRIGGER
CREATE TRIGGER t_audit_task AFTER INSERT OR UPDATE ON task FOR EACH ROW EXECUTE PROCEDURE audit_task();

So the question is, how do I access the row from the original table so I can perform the insert?

Thank you much.

On Tue, Mar 21, 2006 at 09:21:43AM -0500, Larry White wrote:
> I can't figure out how to pass parameters to a trigger function.
>
> I checked the documentation and saw that trigger functions don't take
> params in the usual fashion,
> but couldn't find an example of a pl-sql trigger function that used
> the original row data within the function.

Check the docs. It varies by language, but this is for plpgsql:

http://www.postgresql.org/docs/8.0/static/plpgsql-trigger.html

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

On Tuesday 21 March 2006 09:21 am, Larry White saith:
> I can't figure out how to pass parameters to a trigger function.
>
> I checked the documentation and saw that trigger functions don't take
> params in the usual fashion,
> but couldn't find an example of a pl-sql trigger function that used
> the original row data within the function.
>
> What I want is an on update trigger that creates an entry in a second
> table.  The second (history)  table has a subset of the columns in the
> first.
>
> Here's what I have so far:
>
> -- THIS IS WRONG - CANT PASS PARAMS INTO TRIGGER FUNCTION
>
> CREATE OR REPLACE FUNCTION audit_task ("param type declarations were
> here") RETURNS TRIGGER AS '
>     -- create an audit trail record
> BEGIN
>     -- Perform the insert
>
>     INSERT INTO TASK_h  (id,
>                  updated_by,
>              updated,
>              name,
>              description
>              )
>         VALUES ($1, $2, $3, $4, $5);
>
>     RETURN NULL;
> END;
>
> ' LANGUAGE plpgsql;
>
>
> -- THE TRIGGER
> CREATE TRIGGER t_audit_task AFTER INSERT OR UPDATE ON task FOR EACH
> ROW EXECUTE PROCEDURE audit_task();
>
> So the question is, how do I access the row from the original table so I
> can perform the insert?
>
> Thank you much.


If I understand your question correctly, this documentation addresses your
problem:
37.10. Trigger Procedures


 PL/pgSQL can be used to define trigger procedures. A trigger procedure is
created with the CREATE FUNCTION command, declaring it as a function with no
arguments and a return type of trigger. 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.


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


NEW

 Data type RECORD; variable holding the new database row for INSERT/UPDATE
operations in row-level triggers. This variable is null in statement-level
triggers.

OLD

 Data type RECORD; variable holding the old database row for UPDATE/DELETE
operations in row-level triggers. This variable is null in statement-level
triggers.

TG_NAME

 Data type name; variable that contains the name of the trigger actually
fired.

TG_WHEN

 Data type text; a string of either BEFORE or AFTER depending on the trigger's
definition.

TG_LEVEL

 Data type text; a string of either ROW or STATEMENT depending on the
trigger's definition.

TG_OP

 Data type text; a string of INSERT, UPDATE, or DELETE telling for which
operation the trigger was fired.

TG_RELID

 Data type oid; the object ID of the table that caused the trigger invocation.

TG_RELNAME

 Data type name; the name of the table that caused the trigger invocation.

TG_NARGS

 Data type integer; the number of arguments given to the trigger procedure in
the CREATE TRIGGER statement.

TG_ARGV[]

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



A trigger function must return either null or a record/row value having
exactly the structure of the table the trigger was fired for.


Row-level triggers fired BEFORE may return null to signal the trigger manager
to skip the rest of the operation for this row (i.e., subsequent triggers are
not fired, and the INSERT/UPDATE/DELETE does not occur for this row). If a
nonnull value is returned then the operation proceeds with that row value.
Returning a row value different from the original value of NEW alters the row
that will be inserted or updated (but has no direct effect in the DELETE
case). To alter the row to be stored, it is possible to replace single values
directly in NEW and return the modified NEW, or to build a complete new
record/row to return.


The return value of a BEFORE or AFTER statement-level trigger or an AFTER
row-level trigger is always ignored; it may as well be null. However, any of
these types of triggers can still abort the entire operation by raising an
error.


Example 37-1 shows an example of a trigger procedure in PL/pgSQL.


Example 37-1. A PL/pgSQL Trigger Procedure


 This example trigger ensures that any time a row is inserted or updated in
the table, the current user name and time are stamped into the row. And it
checks that an employee's name is given and that the salary is a positive
value.

CREATE TABLE emp (
    empname text,
    salary integer,
    last_date timestamp,
    last_user text
);

CREATE FUNCTION emp_stamp() RETURNS trigger AS '
    BEGIN
        -- Check that empname and salary are given
        IF NEW.empname IS NULL THEN
            RAISE EXCEPTION ''empname cannot be null'';
        END IF;
        IF NEW.salary IS NULL THEN
            RAISE EXCEPTION ''% cannot have null salary'', NEW.empname;
        END IF;

        -- Who works for us when she must pay for it?
        IF NEW.salary < 0 THEN
            RAISE EXCEPTION ''% cannot have a negative salary'', NEW.empname;
        END IF;

        -- Remember who changed the payroll when
        NEW.last_date := ''now'';
        NEW.last_user := current_user;
        RETURN NEW;
    END;
' LANGUAGE plpgsql;

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
    FOR EACH ROW EXECUTE PROCEDURE emp_stamp();

On Tue, 21 Mar 2006, Larry White wrote:

> I can't figure out how to pass parameters to a trigger function.
>
> I checked the documentation and saw that trigger functions don't take
> params in the usual fashion,
> but couldn't find an example of a pl-sql trigger function that used
> the original row data within the function.
>
> What I want is an on update trigger that creates an entry in a second
> table.  The second (history)  table has a subset of the columns in the
> first.
>
> Here's what I have so far:
>
> -- THIS IS WRONG - CANT PASS PARAMS INTO TRIGGER FUNCTION
>
> CREATE OR REPLACE FUNCTION audit_task ("param type declarations were
> here") RETURNS TRIGGER AS '
>     -- create an audit trail record
> BEGIN
>     -- Perform the insert
>
>     INSERT INTO TASK_h  (id,
>                  updated_by,
>              updated,
>              name,
>              description
>              )
>         VALUES ($1, $2, $3, $4, $5);
>
>     RETURN NULL;
> END;
>
> ' LANGUAGE plpgsql;
>
>
> -- THE TRIGGER
> CREATE TRIGGER t_audit_task AFTER INSERT OR UPDATE ON task FOR EACH
> ROW EXECUTE PROCEDURE audit_task();
>
> So the question is, how do I access the row from the original table so I can
> perform the insert?

The old row is OLD and the new row NEW and do not need to be declared as
arguments (in fact trigger functions are always currently created without
declared arguments).  I think section 36.10 in the 8.1 docs has info for
other implicit arguments to plpgsql trigger functions.

Version: 8.1.3
System: RedHat Linux 3.0 ES

In doing a bulk load with libpq (PQexec, PQputCopyData, PQputCopyEnd), the
COPY is failing with:

  COPY (d) command did not complete successfully:
  ERROR:  end-of-copy marker corrupt CONTEXT:  COPY detail, line 47201: ""

There are 50,000 lines in this transaction, so this is not the last line.
Other postings indicate this is from a '\.'.  On line 47201, I do see a '\.'
embedded in the middle of the line.  The documentation states that this
should be a problem only if those are the only two characters on a line.

A posting from December 2005 against 8.1.0 (bug #2114) seems to indicate
this should be fixed.

Am I missing something, or is there still a problem?  Do I still need to
change '\.\ to '\\.'?

Should I be using PQescapeString on strings being passed to COPY?

Wes