pl/pgsql trigger: syntax error at or near "ELSEIF"
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
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
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.
# 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'' THENYou 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