Re: Problem with trigger function

On Wed, 3 Sep 2008, Mira Dimitrijevic wrote:

> Hi,
> I wrote the trigger function below and when trying to execute it, I
> get the following error:
>
> 15:00:42 [CREATE - 0 row(s), 0.000 secs] [Error Code: 0, SQL
> State: 42601] ERROR: syntax error at or near "INSERT"
>
> I am using DBVisualizer's "SQL Commander" window, not the "create
> funciton" option. I know the SQL is correct - I can execute it just
> by itself. The problem is somewhere in the
> syntax for a postgres trigger function. Any input on what the problem
> might be would be just fabulous!

It looks like you're not always quoting properly if the below is an exact
quote. For example you double the single quotes in the IF tg_op =
''INSERT'' but don't double them in the VALUES ('INSERT').

You might find it easier to use dollar quoting for the function body
(using $$ instead of ' to wrap the body) if you're using a version
that supports it which will mean you shouldn't need to double any of the
quotes inside the function.

> CREATE OR REPLACE FUNCTION audit_sequence_update() RETURNS trigger AS '
> BEGIN
> IF tg_op = ''INSERT'' THEN
> INSERT INTO audit_sequence_update(operation, "day",
> owner_uid, "sequence")
> VALUES
> ('INSERT', NEW."day", NEW.owner_uid, NEW."sequence");
> ELSE
> INSERT INTO audit_sequence_update(operation, "day",
> owner_uid, "sequence")
> SELECT
> 'UPDATE', "day", owner_uid, "sequence"
> FROM sequence where isdid=NEW.isdid;
> END IF;
> RETURN NEW;
> END
> ' LANGUAGE plpgsql;