Hi
Please can you help
me with the following trigger I have written in PostgreSQL7.4.8 running
under Fedora Linux, using pgAdmin III as client.
Forevents in a
system, I
wanted to break up a string which is a log statement (inserted intoevent
table)into key-value pairs (where the string is '|' delimited)and
insert them into a separate table (event_value). I havewritten 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
LOOPBEGINeventkey := 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
BEGINeventvalue :=
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_eventAFTER INSERTON event
FOR EACH
ROWEXECUTE PROCEDURE fn_event();
Then I createa
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
followinginsert 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 THENEXECUTE \'INSERT INTO strings_tbl
VALUES ( \' ||
