Thread: 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.1 20050727 (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)
> tbl
>
> I 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/
> 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?




--
Verba volent, scripta manent
Dan Black