Thread: Trigger and Trigger function, Part 2

Thanks for the assistance Michael Fuhr and Dr. DeSoi. The recoded function is much more what I wanted to anyway - one
genericfunction that could be used on multiple tables through their respective triggers. The new function I am trying
is:

  CREATE 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' VOLATILE;

However, PostgreSQL is STILL giving me a syntex error on line two of the above function. The exact text is:

  ERROR: syntax error at or near "FUNCTION" at character 16.

This error gives me nightmares of QBasic from 1982. Syntex error indeed. I have included a screenshot from pgAdmin III
toshow you. This is why I am so stumped - the samples from the documentation do not work for me. Could this be an
installationor configuration issue? Does anyone see why this is not working for me? 

--Matthew







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 Fri, Jun 17, 2005 at 09:37:23AM -0700, Matt Iskra wrote:
>
>   CREATE 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' VOLATILE;
>
> However, PostgreSQL is STILL giving me a syntex error on line two
> of the above function. The exact text is:
>
>   ERROR: syntax error at or near "FUNCTION" at character 16.

I created an oc schema and used psql to copy the above code into a
database with no problems.  Have you tried using psql instead of
pgAdmin III?

In the image you attached, there's a marker on the left side of the
line that contains oc.upd_last_touch(), and it looks like the cursor
is just after the dot.  Are those significant, perhaps showing where
the problem is?  I don't use pgAdmin III -- does it have trouble
with schema-qualified function names?  What happens if you omit
"oc." and make the function name simply upd_last_touch?

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

On Jun 17, 2005, at 12:37 PM, Matt Iskra wrote:

> However, PostgreSQL is STILL giving me a syntex error on line two of
> the above function. The exact text is:
>
>   ERROR: syntax error at or near "FUNCTION" at character 16.


The function as written works for me. I don't think it is a missing
schema error because in that case you would see a different error. My
best guess is there is some kind of invisible bad character there. Try
this: put the cursor in front of 'oc.' and backspace until it is next
to 'FUNCTION'. Then hit one space and try again.

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