Re: Update rule on a view - what am I doing wrong

Hello,

I have been fighting a problem with an update rule on a view. I have a view that combines two tables where the 'sub' table (scont) can have several rows per row in the 'top' table (icont). The view combines these to show only one record per row in the top table. To be able to update on this view I have created a rule 'on update'. The rule needs to have both UPDATE, DELETE, and INSERT commands. Is this not possible or am I doing something else wrong ?

In the included sql script I have tried to show the problem. When the final update statement is executed, I get a 'duplicate key violation' on a record that has never been there.

I am using PostgreSQL version 9.1.7 (running on Linux/Ubuntu 12.04). What am I doing wrong ?

Please help,

Leif

Leif Jensen <leif(at)crysberg(dot)dk> writes:
> I have been fighting a problem with an update rule on a view. I have a view that combines two tables where the 'sub' table (scont) can have several rows per row in the 'top' table (icont). The view combines these to show only one record per row in the top table. To be able to update on this view I have created a rule 'on update'. The rule needs to have both UPDATE, DELETE, and INSERT commands. Is this not possible or am I doing something else wrong ?

Multiple commands in a view rule are pretty squishy --- I think the
earlier statements in your rule list are probably changing the view's
output and thus affecting the behavior of later statements. You're
also going to have lots of unpleasant surprises as soon as you try
to use any volatile functions (eg nextval()) with this.

9.1 has INSTEAD OF triggers, so I'd strongly recommend seeing if you can
use those instead of rules.

regards, tom lane

On 2013-01-18, Leif Jensen <leif(at)crysberg(dot)dk> wrote:

> I have been fighting a problem with an update rule on a view. I
> have a view that combines two tables where the 'sub' table (scont) can
> have several rows per row in the 'top' table (icont). The view
> combines these to show only one record per row in the top table. To be
> able to update on this view I have created a rule 'on update'. The
> rule needs to have both UPDATE, DELETE, and INSERT commands. Is this
> not possible or am I doing something else wrong ?

when I hit that issue in 8.4 i used a plpgsql function

... do instead select update_rule_func(old,new);

--
⚂⚃ 100% natural

Hi Jasen.

Thank you for your response (also thank you to Tom).

I have now tried your suggestion, but I'm not sure how you have implemented the plpgsql function. When I create the function: CREATE update_rule_func( old record, new record ) AS ... I am told, that I cannot use record for the parameter type. Could you please expand a little on your example ?

Leif

----- "Jasen Betts" <jasen(at)xnet(dot)co(dot)nz> wrote:

> On 2013-01-18, Leif Jensen <leif(at)crysberg(dot)dk> wrote:
>
> > I have been fighting a problem with an update rule on a view. I
> > have a view that combines two tables where the 'sub' table (scont)
> can
> > have several rows per row in the 'top' table (icont). The view
> > combines these to show only one record per row in the top table. To
> be
> > able to update on this view I have created a rule 'on update'. The
> > rule needs to have both UPDATE, DELETE, and INSERT commands. Is
> this
> > not possible or am I doing something else wrong ?
>
> when I hit that issue in 8.4 i used a plpgsql function
>
> ... do instead select update_rule_func(old,new);
>
> --
> ⚂⚃ 100% natural
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Hi Leif,

Am 22.01.2013 14:34, schrieb Leif Jensen:
> CREATE update_rule_func( old record, new record ) AS ... I am told, that I cannot use record for the parameter type. Could you please
You should use your view instead of 'record' as parameter type, i.e. CREATE update_rule_func( old
V_YOUR_VIEW, new V_YOUR_VIEW ) AS ... . Also, I'm not sure if 'new' and 'old' are reserved keywords
in PostgreSQL, so you might want to choose different names for your parameters if you still have
trouble with that function.

Marc