BUG #5611: SQL Function STABLE promoting to VOLATILE

The following bug has been logged online:

Bug reference: 5611
Logged by: Brian Ceccarelli
Email address: bceccarelli(at)net32(dot)com
PostgreSQL version: 8.4.4
Operating system: Windows XP 32 bit and Red Hat 5.4 64 bit
Description: SQL Function STABLE promoting to VOLATILE
Details:

----------------------------------------------------------------------------
-----
--
-- Demonstration of a PL/PGSQL stable-scoped function not working.
--
-- To see the problem, run this entire script from PGAdmin. Bring
up Task Manager too.
-- After you the run this entire script, you can repeat the specific
problem by running:
--
-- select * from f_pass_4();
--
-- Note that "I am in f_return_ver_id_4() repeats once for every row
returned from f_get_table_4().
-- Postgres should only call f_return_ver_id_4() once.

-- The Problems:
--
-- 1. It seems that STABLE functions called within a SQL language
get promoted to VOLATILE.
-- Even though I declare a function STABLE, Postgres calls it
multiple times within a tranasaction.
--
-- 2. The raise notice in f_return_ver_id_4() also causes a memory
leak in PGAdmin (on Windows).
--
-- Related Problems:
--
-- Even the now() function gets called repeatedly within a stable
SQL function.
--
-- Postgres Version:
--
-- I am running Postgres 8.4.4-1 on Windows. (Windows XP 32 bit)
-- Same problem occurs on Postgres 8.4.4 on Linux-64. Red Hat 5.4.
--
-- Problem NOT in Postgres 8.2.
--
-- This problem does not happen in Postgres 8.2.
--
----------------------------------------------------------------------------
-----

drop type if exists type_pass_test cascade;
create type type_pass_test as (ver_id int8);

CREATE OR REPLACE FUNCTION f_get_table_4()
RETURNS setof type_pass_test AS
$BODY$
----------------------------------------------------------------------------
-----
--
--
----------------------------------------------------------------------------
-----
declare
r type_pass_test;
i int8;
begin

for i in 1..5
loop
r.ver_id := i;
return next r;
end loop;

return;
end;
$BODY$
language 'plpgsql' volatile;

CREATE OR REPLACE FUNCTION f_return_ver_id_4()
RETURNS int8 AS
$BODY$
----------------------------------------------------------------------------
-----
--
--
----------------------------------------------------------------------------
-----
declare
begin
raise notice 'I am in f_return_ver_id_4()';
return 1;
end;
$BODY$
language 'plpgsql' stable;

CREATE OR REPLACE FUNCTION f_do_4(ver_id_in int8)
RETURNS setof type_pass_test AS
$BODY$
----------------------------------------------------------------------------
-----
--
-- When you run f_pass_4(), f_pass_4() calls f_do_4() passing ver_id_in as
f_return_ver_id_4().
--
-- The error:
--
-- Even though f_return_ver_id_4() is a STABLE function, the select
-- statement below calls f_return_ver_id_4() once for every row coming
back from
-- f_get_table_4().
--
-- The repeat appears when I write the function in the SQL language.
-- The repeat disappears when I write the function in the PL/PGSQL
language.
-- If I add now() to the where clause, you will even see that Postgres
calls now() multiple times.
--
----------------------------------------------------------------------------
-----

select *
from f_get_table_4()
where ver_id = $1;

$BODY$
language 'SQL' stable;

/*
CREATE OR REPLACE FUNCTION f_do_4(ver_id_in int8)
RETURNS setof type_pass_test AS
$BODY$
----------------------------------------------------------------------------
-----
--
-- If I make the f_do_4(ver_id_in int8) a PL/PGSQL function, the problem
goes away.
--
----------------------------------------------------------------------------
-----
begin

return query
select *
from f_get_table_4()
where ver_id = $1;

return;

end;
$BODY$
language 'plpgsql' stable;

*/

CREATE OR REPLACE FUNCTION f_pass_4()
RETURNS int4 AS
$BODY$
----------------------------------------------------------------------------
-----
--
-- Example:
--
-- select * from f_pass_4();
--
----------------------------------------------------------------------------
-----
declare
rows_affected_w int4;
begin

select into rows_affected_w
count(*)
from f_do_4(f_return_ver_id_4());

return rows_affected_w;
end;
$BODY$
language 'plpgsql' stable;

----------------------------------------

select * from f_pass_4();