RE: sql/trigger question...arguments?

On Fri, 29 Sep 2000, chris markiewicz wrote:

> this is a follow-up to my previous question (shown below).  i want to do the
> following...when a row is inserted into my PERSON table, i want to add a row
> to the RESOURCE table.  i checked the documentation and tried many
> things...none seem to work.
>
> questions:
>
> 1. can a postgres trigger contain a insert/select/update/delete statement or
> can it contain only a function call?

Well, it calls a function, although that could be a pl function that does
lots of other things.

> 2. my procedure, if i have to write one, does not return anything.  how do i
> handle that in the RETURNS clause?  if i tell it to return, for example, an
> int4, then try to create my trigger, it tells me that the sp has to return a
> value of type OPAQUE.

You can make a function return opaque.  (Use return NEW; to end the
function) -- see below

> 3. back to the original question, how do i pass variables?  is it the
> :new.PersonID notation?

Triggers get the new row passed to it.  The details depend
somewhat on the language in question.  In pl/pgsql, you can use
NEW.<column> to refer to a column in the new row.

There's some details in sections 10 (11, 12) of the user's guide including
a pl/pgsql example trigger:

CREATE TABLE emp (
    empname text,
    salary int4,
    last_date datetime,
    last_user name);

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

        -- Who works for us when she must pay for?
        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 := getpgusername();
        RETURN NEW;
    END;
' LANGUAGE 'plpgsql';

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