"Brian Ceccarelli" <bceccarelli@net32.com> writes:
> -- 1. It seems that STABLE functions called within a SQL language
> get promoted to VOLATILE.
That has got nothing to do with it. The change in behavior from 8.2
is due to the fact that set-returning SQL functions can now be inlined.
The statement in f_pass_4(),
select into rows_affected_w count(*)
from f_do_4(f_return_ver_id_4());
gets expanded (by inlining of f_do_4) into
select into rows_affected_w count(*)
from f_get_table_4()
where ver_id = f_return_ver_id_4();
and then since f_get_table_4() returns multiple rows, the WHERE clause
gets evaluated multiple times.
As near as I can tell, your real complaint is that the side-effects of
f_return_ver_id_4 (ie, the RAISE NOTICE) happen more than once.
However, a function declared STABLE really shouldn't *have* any side
effects, because that marking authorizes the optimizer to assume that it
doesn't. If you marked it VOLATILE then this optimization wouldn't
happen.
> -- 2. The raise notice in f_return_ver_id_4() also causes a memory
> leak in PGAdmin (on Windows).
Hm, you probably ought to mention that part on the pgadmin mailing
lists. I don't know whether the appropriate people will notice it here.
regards, tom lane