pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Rule Creates Duplicates retry 2+ messages / 2 participants [nested] [flat]
* Rule Creates Duplicates retry @ 2003-02-18 17:57 Martin Davidsson <mhdavids@ncsu.edu> 0 siblings, 1 reply; 2+ messages in thread From: Martin Davidsson @ 2003-02-18 17:57 UTC (permalink / raw) To: pgsql-sql 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Rule Creates Duplicates retry @ 2003-02-19 07:39 Tom Lane <tgl@sss.pgh.pa.us> parent: Martin Davidsson <mhdavids@ncsu.edu> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2003-02-19 07:39 UTC (permalink / raw) To: Martin Davidsson <mhdavids@ncsu.edu>; +Cc: pgsql-sql "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 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2003-02-19 07:39 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2003-02-18 17:57 Rule Creates Duplicates retry Martin Davidsson <mhdavids@ncsu.edu> 2003-02-19 07:39 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox