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/