Thread: Rule Creates Duplicates retry

Rule Creates Duplicates retry

From

"Martin Davidsson"

Date:

18 February 2003, 15:58:22

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

Re: Rule Creates Duplicates retry

From

Tom Lane

Date:

19 February 2003, 05:39:57

"Martin Davidsson" <mhdavids@ncsu.edu> 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