Обсуждение: IF- statements in a rule's 'DO INSTEAD SELECT ...'- statement
Hello, all,
Can I insert IF- statements into a rule's DO INSTEAD- clause ? I have the
problem in a situation like :
create rule vprinsert as on insert to vpr_status
do instead (DDDDD);
if I insert as DDDDD a genuine SELECT- statement (or, Alvaro, several select
statements), everything is OK; but if I start inserting IF- statements, it's
not OK any more (syntax errors). Don't I just know the exact syntax, or is
this simply impossible ?
Anyone any idea on how to resolve this ?
Regads,
Philippe Bertin.
P.S. To have an idea why I want to insert IF- statements into the rule,
please refer to my newsgroup posting of last Friday ...
On Mon, 6 May 2002, Bertin, Philippe wrote: > Hello, all, > > Can I insert IF- statements into a rule's DO INSTEAD- clause ? I have the > problem in a situation like : > > create rule vprinsert as on insert to vpr_status > do instead (DDDDD); > > if I insert as DDDDD a genuine SELECT- statement (or, Alvaro, several select > statements), everything is OK; but if I start inserting IF- statements, it's > not OK any more (syntax errors). Don't I just know the exact syntax, or is > this simply impossible ? > > Anyone any idea on how to resolve this ?> > > Regads, > > Philippe Bertin. > > P.S. To have an idea why I want to insert IF- statements into the rule, > please refer to my newsgroup posting of last Friday ... Sorry, can't remember that posting. However, what you need to do is create a function which does what you want to do on insert to your object and then have a do instead rule that simply does: SELECT my_on_insert_fn(NEW.field1, NEW.field2, ...) I've been told it should be possible to pass NEW into the function as a record of the correct type (i.e. the attached object's name), however, I couldn't get that to work and so ended up listing the fields as individual parameters. If anyone can give me any more hints on this I'd appreciate it since I'm on the 16 parameter default limit. -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants
Hi Alvaro, Hi Nigel, Thanks for your reply. I indeed already tried with a plpgsql function. But that's just my problem : if I call a function from within a view's rule, this function is not executed anymore with the same rights as a user had on the view. So if a user may access a view, but not the table behind, calling a function in the DO INSTEAD- clause will not execute the function with the proper (view) rights on the table ... (to all) Could anyone - (developers, eventually ?) explain me why the (security) context of a function call is not passed along when the function gets called from within a view ? I think this feature is for sure not superfluous, and I could consider having a look into the code to have this changed (but I think this is a VERY big pile of source codes I never ever looked at before, so this would take a lot of efforts ... for me) Kind regards, Philippe Bertin.
On Tue, 7 May 2002, Bertin, Philippe wrote: Hi Phillippe, > Thanks for your reply. I indeed already tried with a plpgsql function. But > that's just my problem : if I call a function from within a view's rule, > this function is not executed anymore with the same rights as a user had on > the view. So if a user may access a view, but not the table behind, calling > a function in the DO INSTEAD- clause will not execute the function with the > proper (view) rights on the table ... Oh, sure, you are right. > (to all) Could anyone - (developers, eventually ?) explain me why the > (security) context of a function call is not passed along when the function > gets called from within a view ? I think this feature is for sure not > superfluous, and I could consider having a look into the code to have this > changed (but I think this is a VERY big pile of source codes I never ever > looked at before, so this would take a lot of efforts ... for me) That feature is added in current CVS I think. Maybe you can look at current sources and backport the patch. -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "La verdad no siempre es bonita, pero el hambre de ella si"