Re: [GENERAL] Update rule on a view - what am I doing wrong
Hi Marc, Thanks a lot. That works fine. The names 'NEW' and 'OLD' works fine. Leif - "Marc Schablewski" 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Update rule on a view - what am I doing wrong
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
Re: [GENERAL] Update rule on a view - what am I doing wrong
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" wrote: > On 2013-01-18, Leif Jensen 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Update rule on a view - what am I doing wrong
On 2013-01-18, Leif Jensen 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
Re: [GENERAL] Update rule on a view - what am I doing wrong
Leif Jensen 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
