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...
Date: Mon, 5 Sep 2005 21:30:35 -0700 (PDT)
Message-ID: <20050905212707.Y5782@megazone.bigpanda.com> (raw)
In-Reply-To: <DD7AA1EE8BCA9C4184AE33947FC6CD5201493C20@mx01srv.london.wavoo.com>
References: <DD7AA1EE8BCA9C4184AE33947FC6CD5201493C20@mx01srv.london.wavoo.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone.bigpanda.com, ASohal@voxgen.com
Subject: Re: Weird Trigger Behaviour using IF ... THEN ... ESLEIF...
In-Reply-To: <20050905212707.Y5782@megazone.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox