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();