Rules WHERE condition

From: Robert Fitzpatrick <robert(at)webtent(dot)com>
To: PostgreSQL <pgsql-general(at)postgresql(dot)org>
Subject: Rules WHERE condition
Date: 2004-11-17 16:20:41
Message-ID: 1100708441.29305.7.camel@columbus.webtent.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Oleg Bartunov 2004-11-17 16:26:57 Re: TSearch2: Problems with compound words and stop words
Previous Message Bill Harris 2004-11-17 16:16:56 Moving from 7.x to 8.0beta4 with a backup