Update rule on a view - what am I doing wrong

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 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

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

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@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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general