Re: [SQL] ERROR: DefineIndex: index function must be marked iscachable

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.