Обсуждение: 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'' 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