Re: Weird Trigger Behaviour using IF ... THEN ... ESLEIF...

On Mon, 5 Sep 2005, Antony Sohal wrote:

> Please can you help me with the following trigger I have written in
> PostgreSQL 7.4.8 running under Fedora Linux, using pgAdmin III as
> client.

> Now I create a trigger on event table as :
>
> CREATE OR REPLACE FUNCTION fn_event()
>   RETURNS "trigger" AS
> '
>   DECLARE
>     -- Declare a variable to hold the event id.
>     eventid       INTEGER;
>     logme         VARCHAR;
>     eventvalue_id INTEGER;
>     delimiter     VARCHAR DEFAULT \'|\';
>     -- Declare a variable to key and value.
>     eventkey      VARCHAR;
>     eventvalue    VARCHAR;
>
>   BEGIN
>     eventid := NEW.event_id;
>     logme := substring(NEW.log_statement FROM position(delimiter IN
> NEW.log_statement)+1 FOR length(NEW.log_statement));
>
>     WHILE length(logme) > 0 AND position(delimiter IN logme) > 0 LOOP
>  BEGIN
>   eventkey := substring(logme FROM 0 FOR position(delimiter IN logme));
>   logme := substring(logme FROM (position(delimiter IN logme)+1) FOR
> length(logme));
>
>   IF position(delimiter IN logme) = 0 THEN
>       BEGIN
>    eventvalue := logme;
>       END;
>   ELSE
>       BEGIN
>    eventvalue := substring(logme FROM 0 FOR position(delimiter IN
> logme));
>       END;
>   END IF;
>   logme := substring(logme FROM position(delimiter IN logme) + 1 FOR
> length(logme));
>   SELECT INTO eventvalue_id nextval(\'event_sequence\');
>   EXECUTE \'INSERT INTO event_value (event_value_id, event_id,
> event_key, event_value) VALUES (\' ||
>      eventvalue_id || \',\' || eventid || \',\' ||
> quote_literal(eventkey) || \',\' || quote_literal(eventvalue) || \')\';
>  END;
>      END LOOP;
>      RETURN NULL;
>  END;
>  '
>   LANGUAGE 'plpgsql' VOLATILE;
>
>
> CREATE TRIGGER tg_event AFTER INSERT ON event
>   FOR EACH ROW EXECUTE PROCEDURE fn_event();
>
> Then I create a trigger on event_value:
>
> CREATE OR REPLACE FUNCTION fn_event_value() RETURNS TRIGGER AS '
> DECLARE
>     -- Declare a variable to key and value.
>     id          INTEGER;
> BEGIN
>     EXECUTE \'INSERT INTO strings_tbl VALUES ( \' ||
> quote_literal(NEW.event_key) || \')\';
>     IF \'APPLICATION\' = NEW.event_key THEN
>         EXECUTE \'INSERT INTO strings_tbl VALUES ( \' ||
> quote_literal(NEW.event_value) || \')\';
>         RETURN NULL;
>     END IF;
>     IF \'CLI\' = NEW.event_key THEN
>         EXECUTE \'INSERT INTO strings_tbl VALUES ( \' ||
> quote_literal(NEW.event_value) || \')\';
>         RETURN NULL;
>     END IF;
>
>     -- do nothing, nothing at all...
>     EXECUTE \'INSERT INTO strings_tbl VALUES ( \' ||
> quote_literal(\'EMPTY\') || \')\';
>
>     RETURN NULL;
> END;
> ' LANGUAGE plpgsql;

I don't see the ELSEIF in either of the above.

> Now if I change the trigger with this I get the excepted result:
>
> CREATE OR REPLACE FUNCTION fn_event_value() RETURNS TRIGGER AS '
> DECLARE
>     -- Declare a variable to key and value.
>     id          INTEGER;
> BEGIN
>     EXECUTE \'INSERT INTO strings_tbl VALUES ( \' ||
> quote_literal(NEW.event_key) || \')\';
>     IF \'APPLICATION\' = NEW.event_key THEN
>  EXECUTE \'INSERT INTO strings_tbl VALUES ( \' ||
> quote_literal(NEW.event_value) || \')\';
>         RETURN NULL;
>     END IF;
>     IF \'CLI\' = NEW.event_key THEN
>         EXECUTE \'INSERT INTO strings_tbl VALUES ( \' ||
> quote_literal(NEW.event_value) || \')\';
>         RETURN NULL;
>     END IF;
>
>     -- do nothing, nothing at all...
>     EXECUTE \'INSERT INTO strings_tbl VALUES ( \' ||
> quote_literal(\'EMPTY\') || \')\';
>
>     RETURN NULL;
> END;
> ' LANGUAGE plpgsql;
>

>

> However, please can some one explain why IF .... THEN .... ELSEIF .....
> THEN .... ELSEIF .... THEN.... ELSE .... END IF; doesn't work.

IIRC Until 8.0 the correct spelling is ELSIF. I believe 8.0 added ELSEIF
as an alternate spelling.