looks like to index doesn't like to use functions which aren't internal.
In your case though, this is not a problem. Rather than defining function
'myupper' use the internal upper directly in the create index statement:
DROP INDEX upper_name;
CREATE INDEX upper_name ON prosb (upper(defendant_name) varchar_ops);
Don't know what you do if you want to use a function that is not defined
internally.
HTH,
regards,
S.
On Mon, 1 Nov 1999, Geoff Russell wrote:
> Hi,
>
> I want a case independent index on people's names and am having troubles.
>
> Here is some output from a postgres run:
>
> /*
> * First lets drop the tables and index
> */
> drop index upper_name ;
> drop function myupper (text);
> drop table prosb;
> drop sequence prosb_brief_seq;
> /*
> * now make them
> */
> create table prosb (
> animal_s text,
> brief serial primary key,
> defendant_name text ,
> payment_received text
> ... ETC
> );
> NOTICE: CREATE TABLE will create implicit sequence prosb_brief_seq for SERIAL column prosb.brief
> NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index prosb_pkey for table prosb
> CREATE
> create function
> myupper (text)
> returns text
> AS 'SELECT upper($1);' LANGUAGE 'sql';
> CREATE
> /*
> * the index creation works fine
> */
> create index upper_name on prosb (myupper(defendant_name) varchar_ops);
> CREATE
>
> insert into prosb (animal_s, defendant_name) values('cat','John');
> ERROR: internal error: untrusted function not supported.
> insert into prosb (animal_s, defendant_name) values('cat','Steven');
> ERROR: internal error: untrusted function not supported.
> insert into prosb (animal_s, defendant_name) values('dog','Mike');
> ERROR: internal error: untrusted function not supported.
>
> I tried using plpgsql instead of sql, but PG tells me the language isn't
> registered.
>
> Cheers,
> Geoff,
>
> geoff@austrics.com.au | Phone: +618-8332-5069
> 6 Fifth Ave, St Morris, SA 5068 | Fax: +618-8364-1543
>
>
> ************
>
Stuart C. G. Rison
Department of Biochemistry and Molecular Biology
6th floor, Darwin Building, University College London (UCL)
Gower Street, London, WC1E 6BT, United Kingdom
Tel. 0207 504 2303, Fax. 0207 380 7193
e-mail: rison@biochem.ucl.ac.uk