Re: rule for update view that updates/inserts into 2 tables

On Mon, Apr 14, 2008 at 06:03:27PM -0400, Chad Showalter wrote:
> I've posted this on pgsql-general and pgsql-sql, and haven't got any
> responses. If any of you would be able to take a look at this for me and
> give some feedback, I'd be obliged.

I saw it there too, but couldn't spot anything at the time. But I think
I have something now. You say at one point:
> Specifically,
> . after the first call, 1 row is inserted
> . after the second call, 2 rows are inserted
> . after the third call, 4 rows are inserted

Looking at your view definition:
> CREATE OR REPLACE VIEW my_view AS
> SELECT t.my_table_id,t.a,t.b,au.audit_id, au.c
> FROM
> my_table t, my_audit_table au
> WHERE
> t.my_table_id = au.my_table_id;

ISTM that you will get one row in the output for each row in your audit
table, which means indeed after the first update you will now have two
rows in the view for that id and the rule is applied to the result of
the view, which means you get lots of duplicate inserts.

My question is, why do you join to the audit table in the view anyway,
it seems somewhat pointless. But if you really want that, I'd suggest
adding something to the view to limit the number of audit entries to one.

> How do I accomplish what I want to accomplish here? I'd prefer not to use a
> sp.

Why not?

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.