Professional Web Applications Themes

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

Hello, what is the pr 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-# ...

  1. #1

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

    Hello, what is the pr 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

    ---------------------------(end of broadcast)---------------------------
    TIP 7: don't forget to increase your free space map settings

    Roman Neuhauser Guest

  2. #2

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

    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


    ---------------------------(end of broadcast)---------------------------
    TIP 4: Don't 'kill -9' the postmaster

    Pavel Stehule Guest

  3. #3

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

    Roman Neuhauser wrote:
    > Hello, what is the pr trying to tell me? (7.4.2 if it matters)
    > test'# ELSEIF TG_OP = ''DELETE'' THEN
    You typed ELSEIF, the pr 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.

    ---------------------------(end of broadcast)---------------------------
    TIP 9: the planner will ignore your desire to choose an index scan if your
    joining column's datatypes do not match

    Alban Hertroys Guest

  4. #4

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

    # [email]albanmagproductions.nl[/email] / 2005-01-14 13:27:24 +0100:
    > Roman Neuhauser wrote:
    > >Hello, what is the pr trying to tell me? (7.4.2 if it matters)
    > >test'# ELSEIF TG_OP = ''DELETE'' THEN
    >
    > You typed ELSEIF, the pr 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 [url]http://www.eyrie.org./~eagle/faqs/questions.html[/url]

    ---------------------------(end of broadcast)---------------------------
    TIP 1: subscribe and unsubscribe commands go to [email]majordomopostgresql.org[/email]

    Roman Neuhauser Guest

Similar Threads

  1. #38779 [NEW]: Engine crash when "require" file with syntax error through stream wrapper
    By alexander dot netkachev at gmail dot com in forum PHP Bugs
    Replies: 3
    Last Post: September 11th, 02:30 PM
  2. GD.c: 533: error: syntax error before "void"
    By Ken Rich in forum PERL Modules
    Replies: 2
    Last Post: March 28th, 03:40 PM
  3. "Error Creating Control" and "Cast from String"
    By Lisa in forum ASP.NET Building Controls
    Replies: 1
    Last Post: August 14th, 01:53 PM
  4. Acrobat Form Submit error: changes VALUE="true" to "0"
    By Douglas_Maus@adobeforums.com in forum Adobe Acrobat Macintosh
    Replies: 1
    Last Post: August 12th, 05:25 PM
  5. #12029 [Com]: "--with-apxs" can cause the error "MySQL: Unable to save result set in"
    By sietzepost at streetwisedd dot nl in forum PHP Development
    Replies: 1
    Last Post: October 3rd, 09:21 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139