Re: functions marked STABLE not allowed to do INSERT

Am Montag, den 14.11.2005, 13:29 -0500 schrieb Robert Treat:
> On Monday 14 November 2005 10:02, Tino Wildenhain wrote:
> > New in 8.1 it seems functions marked STABLE are
> > not allowed to have any INSERT statement in them.
> >
>
> Try hiding your inserts in seperate volitle sql function that you can select
> inside your stable function. I think the planner won't be smart enough to
> realize what your doing to it.

Now this is really a bug:

=# CREATE OR REPLACE function foo(int) RETURNS int as $$
$# DECLARE f ALIAS FOR $1;
$# BEGIN
$# RETURN (random()*f)::int;
$# END;
$# $$ LANGUAGE plpgsql STABLE;

=# SELECT foo(10);
foo
-----
6
(1 row)

Instead of screaming here, where I use a VOLATILE
function in my STABLE function which could really
be dangerous, it just works.

And the other example, where I do my insert on purpose
and fully knowing what I do gets refused.

Is this a shortcoming of the function compiler?
I dont think so - it retrieves the OID of used
functions anyway so the lookup on stableness
would be easy - and lets skip the silly scan
for INSERT instead.

Regards
Tino