Обсуждение: ERROR: DefineIndex: index function must be marked iscachable
Hi!
Who can help me on that?
First of all, my envoronment is:
Linux netlab142.prodam 2.4.8-26mdk #1 Sun Sep 23 17:06:39 CEST 2001 i686 unknown
pg_ctl (PostgreSQL) 7.2.
pg_ctl (PostgreSQL) 7.2.
Problem: ERROR: DefineIndex: index function must be marked iscachable by executing:
create index bt_proposta_f01 on proposta
using btree (func_cod_secretaria(nr_proponente));
using btree (func_cod_secretaria(nr_proponente));
Where nr_proponte is a integer type column.
The function is:
CREATE OR REPLACE FUNCTION func_cod_secretaria(INTEGER) RETURNS INTEGER AS '
DECLARE
v_nr_proponente ALIAS FOR $1;
BEGIN
return TRUNC(v_nr_proponente/100000,0)*100000
END;
' LANGUAGE 'plpgsql';
DECLARE
v_nr_proponente ALIAS FOR $1;
BEGIN
return TRUNC(v_nr_proponente/100000,0)*100000
END;
' LANGUAGE 'plpgsql';
>\\\!/< 55 11 5080 9283
!_"""_! Elielson Fontanezi
(O) (o) PRODAM - Technical Support Analyst
-------------------------------oOOO--(_)--OOOo-----------------------------------
Success usually comes to those who are too busy to be looking for it.
oooo0 0oooo
---------------------------( )----------( )--------------------------------
\ ( ) /
\_/ \_/
On Fri, 25 Jul 2003, Elielson Fontanezi wrote: > Who can help me on that? > > First of all, my envoronment is: > Linux netlab142.prodam 2.4.8-26mdk #1 Sun Sep 23 17:06:39 CEST 2001 > i686 unknown > pg_ctl (PostgreSQL) 7.2. You should definately move to the highest 7.2 release (7.2.4 I think) which shouldn't require a restore (although you should back up first in any case). I think there were some reasonably important fixes between 7.2.1 and 7.2.4. > CREATE OR REPLACE FUNCTION func_cod_secretaria(INTEGER) RETURNS INTEGER AS ' > DECLARE > v_nr_proponente ALIAS FOR $1; > BEGIN > return TRUNC(v_nr_proponente/100000,0)*100000 > END; > ' LANGUAGE 'plpgsql' Add WITH (iscachable) after LANGUAGE 'plpgsql' to make the function usable in the functional index unless I'm misremembering the old syntax.