Message

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