Thread: Trigger and Trigger function.

Howdy y'all.

I have been trying to get a trigger and trigger function working. I have looked at the docs, copied the examples, and I
stillcannot get this to work. So, now I try you guys/gals. 

My test environment:
  Windows XP Pro (on my laptop)
  User account Postgre, with non-admin premissions
  PostgreSQL 8.0.3 (via msi)
  Installed the pl/pgsql language

I created a test database, named testdb. I created a schema called oc along with its own tablespace. The table creates
OK,but the function that is going to be called by the trigger gets a syntex error on the second line, where the
functionis named. 

My purpose is to get the last_touch field in the table to always have the timestamp when the record was inserted or
updated.

Thanks for the use of your expertise. Source code follows.

--Matthe

/****************************************/

CREATE TABLE oc.optical
(
    id serial NOT NULL,
    created timestamp NOT NULL DEFAULT ('now'::text)::timestamp(6) with time zone,
    last_touch timestamp NOT NULL DEFAULT ('now'::text)::timestamp(6) with time zone,
    file varchar(30),
    CONSTRAINT optical_pk PRIMARY KEY (id) USING INDEX TABLESPACE oc
)
WITHOUT OIDS TABLESPACE oc;
ALTER TABLE oc.optical OWNER TO oc;

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;

ALTER FUNCTION oc.optical_upd() OWNER TO oc;

CREATE TRIGGER optical_trig BEFORE UPDATE ON optical
FOR EACH ROW EXECUTE PROCEDURE upd_last_touch();

/****************************************/




CONFIDENTIALITY NOTICE: This communication with its contents may contain
confidential and/or legally privileged information. It is solely for the
use of the intended recipient(s). Unauthorized interception, review, use
or disclosure is prohibited and may violate applicable laws including
the Electronic Communications Privacy Act. If you are not the intended
recipient, please contact the sender and destroy all copies of the
communication.

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).


John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

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/

On Jun 17, 2005, at 8:59 AM, Michael Fuhr wrote:

>> 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:


Hmm, sorry I don't know what I did the first time. I pasted the code
(removing the annotation) and executed it. I got an "unterminated
dollar quote" error. I changed them to '$$' and then it worked. I must
have had some other stray change that I missed.

For the record, here is the dollar quote format - alphanumeric plus
underscore.


dolq_start        [A-Za-z\200-\377_]
dolq_cont        [A-Za-z\200-\377_0-9]
dolqdelim        \$({dolq_start}{dolq_cont}*)?\$
dolqinside        [^$]+


John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL