X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0EE6C32A5C6 for ; Sun, 3 Oct 2004 12:38:11 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 34988-07 for ; Sun, 3 Oct 2004 11:38:03 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 942A432A5C5 for ; Sun, 3 Oct 2004 12:38:05 +0100 (BST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i93Bc1bx037858 for ; Sun, 3 Oct 2004 11:38:01 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i93BIQs0034670 for pgsql-hackers@postgresql.org; Sun, 3 Oct 2004 11:18:26 GMT From: Gaetano Mendola X-Newsgroups: comp.databases.postgresql.hackers Subject: Re: Mislabeled timestamp functions (was Re: [SQL] [NOVICE] date_trunc'd Date: Sun, 03 Oct 2004 13:18:27 +0200 Organization: PYRENET Midi-pyrenees Provider Lines: 82 Message-ID: <415FE003.9000501@bigfoot.com> References: <20041001182830.GA20624@wolff.to> <13492.1096656574@sss.pgh.pa.us> <15677.1096671183@sss.pgh.pa.us> <415E6A15.1090105@bigfoot.com> <20041002200451.GA14830@wolff.to> <20041003011641.GB19221@wolff.to> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: abuse@pyrenet.fr To: Bruno Wolff III User-Agent: Mozilla Thunderbird 0.8 (Windows/20040913) X-Accept-Language: en-us, en In-Reply-To: <20041003011641.GB19221@wolff.to> X-Enigmail-Version: 0.86.1.0 X-Enigmail-Supports: pgp-inline, pgp-mime To: pgsql-hackers@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/49 X-Sequence-Number: 59552 Bruno Wolff III wrote: > On Sat, Oct 02, 2004 at 15:04:51 -0500, > Bruno Wolff III 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