Re: Trigger and Trigger function.

On Fri, Jun 17, 2005 at 07:55:36AM -0400, John DeSoi wrote:
> On Jun 16, 2005, at 6:56 PM, Matt Iskra wrote:
>
> >CREATE FUNCTION
> >oc.upd_last_touch() <-- syntex error here
> >RETURNS TRIGGER AS
> >$upd_last_touch$
> >BEGIN
> > IF TG_NAME = 'optical_trig' THEN
> > UPDATE optical
> > SET NEW.last_touch = current_timestamp
> > WHERE NEW.id = id;
> > END IF;
> >END;
> >$upd_last_touch$
> >LANGUAGE 'plpgsql' VOLATILE;
>
> I don't think $upd_last_touch$ is a legal dollar quote. It needs to be
> alphanumeric characters only (no underscores).

Eh? Works here in 8.0.3, at least in psql:

test=> CREATE FUNCTION foo() RETURNS integer AS $upd_last_touch$
test$> BEGIN
test$> RETURN 12345;
test$> END;
test$> $upd_last_touch$ LANGUAGE plpgsql;
CREATE FUNCTION
test=> SELECT foo();
foo
-------
12345
(1 row)

Matt, what's telling you where the error is and what exactly is the
error? I see a few things wrong with what you posted:

1. You create a function named oc.upd_last_touch() and subsequently
issue an ALTER FUNCTION statement for oc.optical_upd(), a different
function. Is that correct? You don't show the latter being created.

2. You've created the table oc.optical but the CREATE TRIGGER
statement refers to just optical without a schema qualifier;
likewise with the function upd_last_touch(). Do you have oc
in your search_path?

If I correct the above two problems then the code you posted
successfully loads in my test database (after creating the schema,
tablespace, and user). However, inserting a record into the table
and then updating that record gives the following error:

ERROR: syntax error at or near "$1" at character 21
QUERY: UPDATE optical SET $1 = current_timestamp WHERE $2 = id
CONTEXT: PL/pgSQL function "upd_last_touch" line 3 at SQL statement
LINE 1: UPDATE optical SET $1 = current_timestamp WHERE $2 = id

Instead of doing an UPDATE in the trigger function, you should be
modifying NEW and returning it, like this:

CREATE OR REPLACE FUNCTION oc.upd_last_touch() RETURNS trigger
AS $upd_last_touch$
BEGIN
NEW.last_touch := current_timestamp;
RETURN NEW;
END;
$upd_last_touch$ LANGUAGE plpgsql;

See the "Triggers" chapter in the documentation and the "Trigger
Procedures" section in the PL/pgSQL chapter for more info:

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

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/