From mhdavids@ncsu.edu Tue Feb 18 17:58:21 2003 X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id DA774475AD7 for ; Tue, 18 Feb 2003 12:58:21 -0500 (EST) Received: from uni02mr.unity.ncsu.edu (uni02mr.unity.ncsu.edu [152.1.1.165]) by postgresql.org (Postfix) with ESMTP id A2DAD475AEC for ; Tue, 18 Feb 2003 12:57:11 -0500 (EST) Received: from martin (sgw25774RN.rh.ncsu.edu [152.7.64.98]) by uni02mr.unity.ncsu.edu (8.11.6/8.11.6/N.20030204.02) with ESMTP id h1IHvHn04449 for ; Tue, 18 Feb 2003 12:57:18 -0500 (EST) From: "Martin Davidsson" To: Subject: Rule Creates Duplicates retry Date: Tue, 18 Feb 2003 12:57:12 -0500 Message-ID: <000501c2d777$2a0e1040$62400798@martin> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0006_01C2D74D.41380840" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2616 Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/412 X-Sequence-Number: 11810 This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C2D74D.41380840 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hello, [I sent this email off yesterday, but didn't appear to make it to the list. Hope nobody gets this twice :(] I have a rule that I've been trying to run against a certain table, but it always spits out blank rows, in addition to the rows I want. I have Apach logging to a postgresql 7.3 database with each parameter seperated by a |. I then create a rule to seperate these logs into more logical columns, like this: create rule split_log as on insert to messyTable where message like '%website.url%' do insert into cleanTable values ( split_part(syslogtb.message,' | ',1), split_part(syslogtb.message,' | ',2), ..... split_part(syslogtb.message,' | ',n) ); where n is the number of parameters Apache logs. Without the rule, everything gets properly inserted into mesyTable. I want to rule in place to seperate out the information. But the rule produces a cleanTable that contains the expected stuff, but it also contains duplicates of that stuff, and several empty rows. Is there any obvious way to avoid either? Thanks! // Martin Davidsson ------=_NextPart_000_0006_01C2D74D.41380840 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Message
Hello,
 
[I sent this email off yesterday, but didn't appear t= o make it=20 to the list. Hope nobody gets this twice :(]
 
I have a = rule that=20 I've been trying to run against a certain table, but it always spits out bl= ank=20 rows, in addition to the rows I want.
 
I have Ap= ach logging=20 to a postgresql 7.3 database with each parameter seperated by a |. I then c= reate=20 a rule to seperate these logs into more logical columns, like=20 this:
 
create ru= le=20 split_log as on insert to messyTable where message like '%website.url%' do= =20 insert into cleanTable values (
split_part(syslogtb.message,' |=20 ',1),
split_part(syslogtb.message,' | ',2),
.....
split_part(syslogtb.message,' | ',n)
);
 
where n i= s the=20 number of parameters Apache logs.
 
Wit= hout the=20 rule, everything gets properly inserted into mesyTable. I want to rule in p= lace=20 to seperate out the information. But the rule produces a cleanTable that contains the expected stuff= , but=20 it also contains duplicates of that stuff, and several empty rows. Is there= any=20 obvious way to avoid either?
 
Thanks!
 
// Martin= =20 Davidsson
------=_NextPart_000_0006_01C2D74D.41380840-- From tgl@sss.pgh.pa.us Wed Feb 19 07:39:56 2003 X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 69A01475843 for ; Wed, 19 Feb 2003 02:39:56 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id A2C7847580B for ; Wed, 19 Feb 2003 02:39:55 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.6/8.12.6) with ESMTP id h1J7ds5u009356; Wed, 19 Feb 2003 02:39:55 -0500 (EST) To: "Martin Davidsson" Cc: pgsql-sql@postgresql.org Subject: Re: Rule Creates Duplicates retry In-reply-to: <000501c2d777$2a0e1040$62400798@martin> References: <000501c2d777$2a0e1040$62400798@martin> Comments: In-reply-to "Martin Davidsson" message dated "Tue, 18 Feb 2003 12:57:12 -0500" Date: Wed, 19 Feb 2003 02:39:54 -0500 Message-ID: <9355.1045640394@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/420 X-Sequence-Number: 11818 "Martin Davidsson" writes: > I then create a rule to seperate these logs into more > logical columns, like this: > create rule split_log as on insert to messyTable where message like > '%website.url%' do insert into cleanTable values ( > split_part(syslogtb.message,' | ',1), > split_part(syslogtb.message,' | ',2), > ..... > split_part(syslogtb.message,' | ',n) > ); Not certain, but I think you want to be referring to NEW.message, not messyTable.message (assuming syslogtb is what you meant by messyTable...). If you don't refer to NEW then you are likely to get some bizarre join-of-existing-rows behavior out of this. regards, tom lane