X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 86BD9D1D25D for ; Fri, 20 Feb 2004 15:35:37 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 14983-05 for ; Fri, 20 Feb 2004 11:35:35 -0400 (AST) Received: from internet.potentialtech.com (h-66-167-251-6.PHLAPAFG.covad.net [66.167.251.6]) by svr1.postgresql.org (Postfix) with ESMTP id 8941ED1C511 for ; Fri, 20 Feb 2004 11:35:31 -0400 (AST) Received: from potentialtech.com (pa-plum1c-102.pit.adelphia.net [24.53.179.102]) by internet.potentialtech.com (Postfix) with ESMTP id 29C5069A71 for ; Fri, 20 Feb 2004 10:35:35 -0500 (EST) Message-ID: <40362944.2060809@potentialtech.com> Date: Fri, 20 Feb 2004 10:35:32 -0500 From: Bill Moran User-Agent: Mozilla/5.0 (X11; U; FreeBSD i386; en-US; rv:1.4) Gecko/20031005 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: cacheable stored functions? Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/236 X-Sequence-Number: 5760 I'm converting a SQL application to PostgreSQL. The majority of the logic in this application is in the stored functions in the database. Somewhere, I saw a reference to "WITH (iscachable)" for stored functions, looking again, I'm unable to find any reference to this directive. I have a single function that is _obviously_ safe to cache using this, and it generates no errors or problems that I can see. Now I'm looking at a lot of other functions that, if cached, would speed up performance considerably. Yet I'm reluctant to use this directive since I can't find documentation on it anywhere. Can anyone say whether this is a supported feature in plpgsql, and is safe to use? Is it simply undocumented, or am I just looking in the wrong place? (to reduce ambiguity, the manner in which I'm using this is: CREATE FUNCTION getconstant(VARCHAR) RETURNS int AS ' DECLARE BEGIN IF $1 = ''phrase'' THEN RETURN 1; END IF; ... END; ' LANGUAGE 'plpgsql' WITH (iscacheable); -- Bill Moran Potential Technologies http://www.potentialtech.com