X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 4DCACD7F82
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  6 Sep 2005 01:30:40 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 20056-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  6 Sep 2005 04:30:36 +0000 (GMT)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210])
	by svr1.postgresql.org (Postfix) with ESMTP id 0AD26D7DFB
	for <pgsql-sql@postgresql.org>; Tue,  6 Sep 2005 01:30:35 -0300 (ADT)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id CBCE7354CA; Mon,  5 Sep 2005 21:30:35 -0700 (PDT)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id C3C2E3547F; Mon,  5 Sep 2005 21:30:35 -0700 (PDT)
Date: Mon, 5 Sep 2005 21:30:35 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Antony Sohal <ASohal@voxgen.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Weird Trigger Behaviour using IF ... THEN ... ESLEIF...
In-Reply-To: 
 <DD7AA1EE8BCA9C4184AE33947FC6CD5201493C20@mx01srv.london.wavoo.com>
Message-ID: <20050905212707.Y5782@megazone.bigpanda.com>
References: 
 <DD7AA1EE8BCA9C4184AE33947FC6CD5201493C20@mx01srv.london.wavoo.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.11 required=5 tests=[AWL=-0.097,
	UPPERCASE_25_50=0.207]
X-Spam-Level: 
X-Archive-Number: 200509/55
X-Sequence-Number: 22602

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.