Rules WHERE condition

[Robert Fitzpatrick]

I have a view that used union all to merge three tables together. I was
hoping to create a rule, one for each table using the WHERE condition of
the rule to determine which table gets updated. Is this possible?

This is what I have, assume the view here is a merge of three tables
using union all:

CREATE RULE "update_xrf" AS ON UPDATE TO "public"."viewdeterioratedlbp"
WHERE ((new.note)::text = 'Unit'::text)
DO INSTEAD (UPDATE tblxrf SET deterioration = new.deterioration;);

The note column contains a value that can trigger which table needs to
be updated. I would like to make one of these rules for each table to
update. But when I run the update, it says I have to have an
unconditional rule, is that right? Any suggestions?

ohc=# update viewdeterioratedlbp set note = 'Unit', deterioration =
'test' where xrf_id = 733;
ERROR: cannot update a view
HINT: You need an unconditional ON UPDATE DO INSTEAD rule.

Thanks,
--
Robert
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Michael Fuhr]

On Wed, Nov 17, 2004 at 11:20:41AM -0500, Robert Fitzpatrick wrote:

I have a view that used union all to merge three tables together. I was
hoping to create a rule, one for each table using the WHERE condition of
the rule to determine which table gets updated. Is this possible?

See the CREATE RULE documentation:

http://www.postgresql.org/docs/7.4/s...reaterule.html

In particular, read the last paragraph of the Description section,
the one that begins, "There is a catch if you try to use conditional
rules for view updates...."

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Robert Fitzpatrick]

On Wed, 2004-11-17 at 11:49, Michael Fuhr wrote:

On Wed, Nov 17, 2004 at 11:20:41AM -0500, Robert Fitzpatrick wrote:

I have a view that used union all to merge three tables together. I was
hoping to create a rule, one for each table using the WHERE condition of
the rule to determine which table gets updated. Is this possible?

See the CREATE RULE documentation:

http://www.postgresql.org/docs/7.4/s...reaterule.html

Thanks, that explains a lot, but still not able to get my rule to work,
this is what I have now:

CREATE RULE "update_unconditional" AS ON UPDATE TO
"public"."viewdeterioratedlbp"
DO INSTEAD NOTHING;

CREATE RULE "update_xrf" AS ON UPDATE TO "public"."viewdeterioratedlbp"
WHERE ((new.note)::text = 'Unit'::text)
DO (UPDATE tblxrf SET deterioration = new.deterioration WHERE
(tblxrf.xrf_id = new.xrf_id););

ohc=# update viewdeterioratedlbp set deterioration = 'test' where xrf_id
= 143;
UPDATE 0

This is the first rule I have tried to setup, I read through the doc,
but don't seem to be able to catch what I'm doing wrong. Do I have to
update all fields for it to work?

--
Robert
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Robert Fitzpatrick]

On Wed, 2004-11-17 at 12:49, Robert Fitzpatrick wrote:

On Wed, 2004-11-17 at 11:49, Michael Fuhr wrote:

On Wed, Nov 17, 2004 at 11:20:41AM -0500, Robert Fitzpatrick wrote:

I have a view that used union all to merge three tables together. I was
hoping to create a rule, one for each table using the WHERE condition of
the rule to determine which table gets updated. Is this possible?

See the CREATE RULE documentation:

http://www.postgresql.org/docs/7.4/s...reaterule.html

Thanks, that explains a lot, but still not able to get my rule to work,
this is what I have now:

Forget that last post, it is working even though the UPDATE 0 is
returned. The record did update :)

Please someone let me know if you see any issues with that? Like I said,
my first rule let alone with a WHERE condition.

--
Robert
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match