Re: Parsing the result of a function to a view in an INSERT statement.

----- Original Message -----
>> "Donald Fraser" <demolish(at)cwgsy(dot)net> writes:
>> I have observed that using a function to provide a default value on a colum=
>> n of a view or attempting to parse the result of a function to a view in an=
>> insert or update statement produces undesirable results.
>
> Rules are macros and as such you have to think about multiple
> evaluations of their arguments. You would probably be better off using
> triggers for what you seem to be trying to accomplish.
>
> regards, tom lane

Sorry if my example, which used a rule on a table, was misleading in any way.
It was just part of the overall example and not an insight as to what I am
trying to achieve.

The problem is the evaluation of a function is not happening at the point where
it is first declared. Rather its definition is being passed on to the rule
re-write system and it becomes evaluated at each point where it is referenced
in the rule query tree.

For example: INSERT INTO myview (id, data1, data2) VALUES(get_id('1'),
'2','3');
The result of function get_id('1') is not evaluated first and passed on to the
rules of the view as one would expect. Rather wherever the queries in the rule
system references NEW.id, they appear to be re-written with get_id('1') before
they are run (ok this might not be exactly true but the results show something
to the effect of this). This can lead to strange results when the function
returns a different result each time it is evaluated and you did not anticipate
the user passing such a query that includes the result of such a function.
There is nothing to say that you cannot pass a function to a view, after all a
view is supposed to assimilate a table.

There are obviously ways to work around this obscurity, but it can be difficult
when you don't know how somebody will use a view in advance. The only sure way
that I can see is to combine all the rules into one function and have only one
rule that calls that function.

regards Donald Fraser.