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.