strange stable function behavior
Hi
I have a stable function test.test_stable
CREATE OR REPLACE FUNCTION test.test_stable(int4)
RETURNS int4 AS
$BODY$DECLARE
_param ALIAS FOR $1;
BEGIN
RAISE NOTICE 'ID: %, TIME: %', _param, timeofday()::timestamp;
RETURN _param;
END$BODY$
LANGUAGE 'plpgsql' STABLE STRICT SECURITY DEFINER;
Everything is all right when I execute a simple query
SELECT id, sid FROM
(SELECT *, test.test_stable(id) AS sid FROM generate_series(10, 100) AS id)
tbl
NOTICE: ID: 10, TIME: 2006-06-01 14:57:07.89594
NOTICE: ID: 11, TIME: 2006-06-01 14:57:07.896203
NOTICE: ID: 12, TIME: 2006-06-01 14:57:07.896322
NOTICE: ID: 13, TIME: 2006-06-01 14:57:07.896417
NOTICE: ID: 14, TIME: 2006-06-01 14:57:07.896494
NOTICE: ID: 15, TIME: 2006-06-01 14:57:07.896623
But if I want to display field sid twice
SELECT id, sid, sid FROM
(SELECT *, test.test_stable(id) AS sid FROM generate_series(10, 100) AS id)
tbl
I can see that function test.test_stable executes twice with identical
parameters
NOTICE: ID: 10, TIME: 2006-06-01 14:58:52.950292
NOTICE: ID: 10, TIME: 2006-06-01 14:58:52.950485
NOTICE: ID: 11, TIME: 2006-06-01 14:58:52.950582
NOTICE: ID: 11, TIME: 2006-06-01 14:58:52.950679
NOTICE: ID: 12, TIME: 2006-06-01 14:58:52.950765
NOTICE: ID: 12, TIME: 2006-06-01 14:58:52.950835
NOTICE: ID: 13, TIME: 2006-06-01 14:58:52.9511
NOTICE: ID: 13, TIME: 2006-06-01 14:58:52.975477
NOTICE: ID: 14, TIME: 2006-06-01 14:58:52.992098
NOTICE: ID: 14, TIME: 2006-06-01 14:58:53.008741
NOTICE: ID: 15, TIME: 2006-06-01 14:58:53.025425
NOTICE: ID: 15, TIME: 2006-06-01 14:58:53.058589
Is it bug or special feature?
Postgres
PostgreSQL 8.1.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
4.0.120050727 (Red Hat
4.0.1-5)
--
Verba volent, scripta manent
Dan Black
On Thu, Jun 01, 2006 at 03:09:47PM +0400, Dan Black wrote:
Hi
I have a stable function test.test_stable
<snip>
But if I want to display field sid twice
SELECT id, sid, sid FROM
(SELECT *, test.test_stable(id) AS sid FROM generate_series(10, 100) AS id)
tblI can see that function test.test_stable executes twice with identical
parameters
Postgres makes no special effort to avoid multiple calls of the same
function. Especially since you declared it STABLE.
Is it bug or special feature?
Not a bug nor a special feature, just the way it is. If you put OFFSET 0
in the subquery, that will stop the expansion of the subquery, thus the
function will only be called once.
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
From each according to his ability. To each according to his ability to litigate.
Thank you very much. It works!!!
Where can I read about such features?
2006/6/1, Martijn van Oosterhout <kleptog@svana.org>:
On Thu, Jun 01, 2006 at 03:09:47PM +0400, Dan Black wrote:
Hi
I have a stable function test.test_stable<snip>
But if I want to display field sid twice
SELECT id, sid, sid FROM
(SELECT *, test.test_stable(id) AS sid FROM generate_series(10, 100) ASid)
tbl
I can see that function test.test_stable executes twice with identical
parametersPostgres makes no special effort to avoid multiple calls of the same
function. Especially since you declared it STABLE.Is it bug or special feature?
Not a bug nor a special feature, just the way it is. If you put OFFSET 0
in the subquery, that will stop the expansion of the subquery, thus the
function will only be called once.Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/From each according to his ability. To each according to his ability to
litigate.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)iD8DBQFEftscIB7bNG8LQkwRAjITAJ9csUN2V8oHtfRk280cJYTqkpopIwCfRVQ0
cToHKTMqSf4HD21f+bo3jn0=
=/z/0
-----END PGP SIGNATURE-----
--
Verba volent, scripta manent
Dan Black