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

[Antony Sohal]

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|Voice 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|Voice 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.

 

Vox Generation Limited,Manor House,21 Soho Square, London W1D 3QP, Registered in England: 3937784.

---

---

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

---