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@wolff.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