Postgres Pro
Downloads
Home   >   mailing lists

Thread: pl/pgsql trigger: syntax error at or near "ELSEIF"

pl/pgsql trigger: syntax error at or near "ELSEIF"

From
Roman Neuhauser
Date:
Hello, what is the parser trying to tell me? (7.4.2 if it matters)

test=# CREATE OR REPLACE FUNCTION SYNC_COUPLECOUNT()
test-#   RETURNS TRIGGER
test-#   AS '
test'#     BEGIN
test'#       IF TG_OP = ''INSERT'' THEN
test'#         UPDATE _calls
test'#           SET
test'#             realcouplecount = realcouplecount + 1
test'#           WHERE
test'#             id = NEW.callid;
test'#       ELSEIF TG_OP = ''DELETE'' THEN
test'#         UPDATE _calls
test'#           SET
test'#             realcouplecount = realcouplecount - 1
test'#           WHERE
test'#             id = NEW.callid;
test'#       END IF;
test'#       RETURN NEW;
test'#     END;
test'#   '
test-#   LANGUAGE plpgsql;
CREATE FUNCTION
test=# CREATE TRIGGER triginsdel
test-#   AFTER INSERT OR DELETE ON _couples
test-#   FOR EACH ROW EXECUTE PROCEDURE SYNC_COUPLECOUNT();
CREATE TRIGGER
test=# insert into _couples (id, callid) values (get_next_coupleid(), 1);
ERROR:  syntax error at or near "ELSEIF" at character 1
CONTEXT:  PL/pgSQL function "sync_couplecount" line 8 at SQL statement

On a related note: if I replace NEW with OLD in the second UPDATE (in the
ELSEIF branch), I get:

test=# insert into _couples (id, callid) values (get_next_coupleid(), 1);
ERROR:  record "old" is not assigned yet
DETAIL:  The tuple structure of a not-yet-assigned record is indeterminate.
CONTEXT:  PL/pgSQL function "sync_couplecount" line 8 at SQL statement

What's wrong with that?

--
FreeBSD 4.10-STABLE
12:48PM up 1 day, 6:53, 2 users, load averages: 0.05, 0.05, 0.00

Re: pl/pgsql trigger: syntax error at or near "ELSEIF"

From
Pavel Stehule
Date:
Hello

try


35.7.2.4. IF-THEN-ELSIF-ELSE

IF boolean-expression THEN
    statements
[ ELSIF boolean-expression THEN
    statements
[ ELSIF boolean-expression THEN
    statements
    ...]]
[ ELSE
    statements ]
END IF;

regards
Pavel

Re: pl/pgsql trigger: syntax error at or near "ELSEIF"

From
Alban Hertroys
Date:
Roman Neuhauser wrote:
> Hello, what is the parser trying to tell me? (7.4.2 if it matters)
> test'#       ELSEIF TG_OP = ''DELETE'' THEN

You typed ELSEIF, the parser doesn't know what that means (It's either
ELSIF or ELSE IF).

> On a related note: if I replace NEW with OLD in the second UPDATE (in the
> ELSEIF branch), I get:
> What's wrong with that?

OLD doesn't exist in INSERT triggers, only in UPDATE and DELETE
triggers. A record in an INSERT trigger is by definition a new record,
an old record wouldn't make sense.

IMHO, it would be better to write two or three different triggers (one
on INSERT, the other on DELETE, and maybe a third on UPDATE) than
writing a single and trying to figure out which of those three it was in
the trigger.

Alban Hertroys.

Re: pl/pgsql trigger: syntax error at or near "ELSEIF"

From
Roman Neuhauser
Date:
# alban@magproductions.nl / 2005-01-14 13:27:24 +0100:
> Roman Neuhauser wrote:
> >Hello, what is the parser trying to tell me? (7.4.2 if it matters)
> >test'#       ELSEIF TG_OP = ''DELETE'' THEN
>
> You typed ELSEIF, the parser doesn't know what that means (It's either
> ELSIF or ELSE IF).

    Thanks to everyone who pointed this out for me.

> >On a related note: if I replace NEW with OLD in the second UPDATE (in the
> >ELSEIF branch), I get:
> >What's wrong with that?
>
> OLD doesn't exist in INSERT triggers, only in UPDATE and DELETE
> triggers. A record in an INSERT trigger is by definition a new record,
> an old record wouldn't make sense.
>
> IMHO, it would be better to write two or three different triggers (one
> on INSERT, the other on DELETE, and maybe a third on UPDATE) than
> writing a single and trying to figure out which of those three it was in
> the trigger.

    Thanks, going that route indeed solved the problem.

--
If you cc me or remove the list(s) completely I'll most likely ignore
your message.    see http://www.eyrie.org./~eagle/faqs/questions.html