strange stable function behavior - Mailing list pgsql-general

From Dan Black
Subject strange stable function behavior
Date
Msg-id 27f606250606010409q6a813904t99354f4257a9111f@mail.gmail.com
Whole thread Raw
Responses Re: strange stable function behavior
List pgsql-general
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

pgsql-general by date:

Previous
From: "Riccardo Inverni"
Date:
Subject: Re: SCSI disk: still the way to go?
Next
From: Martijn van Oosterhout
Date:
Subject: Re: strange stable function behavior