Weird Trigger Behaviour using IF ... THEN ... ESLEIF... THEN... ELSEIF... THEN ... ELSE ... END IF;

Hi

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.

For events in a system, I wanted to break up a string which is a log
statement (inserted into event table) into key-value pairs (where the
string is '|' delimited) and insert them into a separate table
(event_value). I have written a trigger to achieve this and it works
fine.

However, I want to execute certain events if the key (that is inserted
into the event_value table) matches certain criteria. To do this I wrote
a another trigger, however I get very strange behaviour. Follow this
example:

I have got the following tables:

CREATE TABLE event (
event_id int4 NOT NULL,
session_id varchar(255),
event_timestamp timestamp,
log_statement varchar(2000),
application varchar(30),
company varchar(30),
environment varchar(30),
CONSTRAINT event_id_pkey PRIMARY KEY (event_id)
);

CREATE TABLE event_value (
event_value_id int4 NOT NULL,
event_id int4 NOT NULL,
event_key varchar(100),
event_value varchar(2000),
CONSTRAINT event_value_id_pkey PRIMARY KEY (event_value_id),
CONSTRAINT fk_event_event_value FOREIGN KEY (event_id) REFERENCES
event (event_id) ON UPDATE NO ACTION ON DELETE NO ACTION
);


CREATE TABLE strings_tbl (
value VARCHAR(256)
);


I have added the PL/pgSQL language to the database, if you have not got
this installed use the following statements:

CREATE OR REPLACE FUNCTION plpgsql_call_handler()
RETURNS language_handler AS
'$libdir/plpgsql', 'plpgsql_call_handler'
LANGUAGE 'c' VOLATILE;

CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
HANDLER plpgsql_call_handler;


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;

Create sequence :

CREATE SEQUENCE event_sequence
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 360
CACHE 10;

Now if you execute the following insert statements:

INSERT INTO event (event_id,session_id, event_timestamp, log_statement)
VALUES
(nextval('event_sequence'),'testsession1',current_timestamp,'|BROWSER|Vo
ice Browser|23|CLI|12334232|HOSTNAME|server
name|THREAD|56|CALL_START|2005-7-29 16:32:25.875|');


I get the following results:
event table has the whole log statement
event_value table has log statement broken up in key value pairs
however, in strings_tbl table I have the following rows :

BROWSER
EMPTY
CLI
EMPTY
HOSTNAME
EMPTY
THREAD
EMPTY
CALL_START
EMPTY

What I expect is the following:

BROWSER
EMPTY
CLI
12334232
HOSTNAME
EMPTY
THREAD
EMPTY
CALL_START
EMPTY


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.


And if I use the following insert statement I get syntax errors

INSERT INTO event (event_id,session_id, event_timestamp, log_statement)
VALUES
(nextval('event_sequence'),'testsession1',current_timestamp,'|BROWSER|Vo
ice Browser 23|CLI|12334232|HOSTNAME|servername|APPLICATION|some
application|THREAD|56|CALL_START|2005-7-29 16:32:25.875|');

Thanks



Antony Sohal

#####################################################################################
Winner - e-Government excellence 2004.
Runner up - European Information Management awards 2004:
- The Premier Project Award.
- B2C Commerce Project Award.
- CRM Project Award.

For more information visit us at www.voxgen.com

#####################################################################################
Note:
This message is for the named person's use only. It may contain confidential,
proprietary or legally privileged information. No confidentiality or privilege
is waived or lost by any mistransmission. If you receive this message in error,
please immediately delete it and all copies of it from your system, destroy any
hard copies of it and notify the sender. You must not, directly or indirectly,
use, disclose, distribute, print, or copy any part of this message if you are not
the intended recipient. Vox Generation Limited and any of its subsidiaries each
reserve the right to monitor all e-mail communications through its networks.

Any views expressed in this message are those of the individual sender, except where
the message states otherwise and the sender is authorised to state them to be the
views of any such entity.

Thank You.
#####################################################################################

#####################################################################################
This e-mail message has been scanned for Viruses and Content and cleared
by NetIQ MailMarshal
#####################################################################################