Thread: 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
perrow in the top table. To be able to update on this view I have created a rule 'on update'. The rule needs to have
bothUPDATE, 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
'duplicatekey 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@crysberg.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
recordper row in the top table. To be able to update on this view I have created a rule 'on update'. The rule needs to
haveboth 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@crysberg.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
parametertype. Could you please expand a little on your example ? 

 Leif


----- "Jasen Betts" <jasen@xnet.co.nz> wrote:

> On 2013-01-18, Leif Jensen <leif@crysberg.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

  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

   Hi Marc,

    Thanks a lot. That works fine. The names 'NEW' and 'OLD' works fine.

 Leif


----- "Marc Schablewski" <ms@clickware.de> wrote:

> 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