strange stable function behavior

From: "Dan Black" <fireworker(at)gmail(dot)com>
To: "pgsql general" <pgsql-general(at)postgresql(dot)org>
Subject: strange stable function behavior
Date: 2006-06-01 11:09:47
Message-ID: 27f606250606010409q6a813904t99354f4257a9111f@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.120050727 (Red Hat
4.0.1-5)

--
Verba volent, scripta manent
Dan Black

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Martijn van Oosterhout 2006-06-01 12:18:36 Re: strange stable function behavior
Previous Message dananrg 2006-06-01 09:44:05 Re: Best open source tool for database design / ERDs?