Re: Mislabeled timestamp functions (was Re: [SQL] [NOVICE] date_trunc'd

Bruno Wolff III wrote:
> On Sat, Oct 02, 2004 at 15:04:51 -0500,
> Bruno Wolff III <bruno(at)wolff(dot)to> wrote:
>
>>On Sat, Oct 02, 2004 at 10:43:01 +0200,
>>
>>There has been such a distinction for a major release or two. "Stable"
>>is how you mark a function that will return the same value within a
>>single transaction.
>
>
> I should have said within a single statement instead of within a single
> transaction.

I know that but a stable function is not called once inside the same query,
instead an immutable is:

sp_immutable() is a simple immutable function
sp_stable() is a simple stable function
sp_foo() is a simple function

test is a table with two rows in it.

regression=# select sp_stable(), sp_immutable(), sp_foo() from test;
NOTICE: sp_immutable called
NOTICE: sp_stable called
NOTICE: sp_foo called
NOTICE: sp_stable called
NOTICE: sp_foo called
sp_stable | sp_immutable | sp_foo
-----------+--------------+--------
0 | 0 | 0
0 | 0 | 0
(2 rows)

so now do you see what do I mean ?

The stable function is threated "stable" only if inserted inside a filter:

regression=# select * from test where sp_stable() = 3;
NOTICE: sp_stable called
a
---
(0 rows)

and from this point of view immutable is not immutable enough:

regression=# select sp_immutable() from test where sp_immutable() = 3;
NOTICE: sp_immutable called
NOTICE: sp_immutable called
sp_immutable
--------------
(0 rows)

Regards
Gaetano Mendola