X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 896F6D1C4BC; Fri, 25 Jul 2003 19:21:17 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 60091-10; Fri, 25 Jul 2003 16:20:30 -0300 (ADT) Received: from megazone.bigpanda.com (unknown [66.180.233.26]) by svr1.postgresql.org (Postfix) with ESMTP id D64A9D1C4B7; Fri, 25 Jul 2003 16:21:15 -0300 (ADT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 43B22D62D; Fri, 25 Jul 2003 12:21:15 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 314F45C0E; Fri, 25 Jul 2003 12:21:15 -0700 (PDT) Date: Fri, 25 Jul 2003 12:21:15 -0700 (PDT) From: Stephan Szabo To: Elielson Fontanezi Cc: pgsql-general , pgsql-sql Subject: Re: [SQL] ERROR: DefineIndex: index function must be marked iscachable In-Reply-To: Message-ID: <20030725121618.G37941-100000@megazone.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/1247 X-Sequence-Number: 46100 On Fri, 25 Jul 2003, Elielson Fontanezi wrote: > What can I do in this case? > I could not found anything about iscachable. > > postgres$ cat in.sql > create index bt_proposta_f01 on proposta > using btree (func_cod_secretaria(nr_proponente)); > > postgres$ psql -d escola -f in.sql > psql:in.sql:2: ERROR: DefineIndex: index function must be marked iscachable Check the create function reference page, I believe you'd need to add WITH (iscachable) to the end of the function creation. iscachable means that the function always returnes the same result when given the same arguments. It's a prereq to using the function in an index. If your function doesn't meet those requirements (for example if it does a query on a table) making an index on that function is a bad idea.