Operator clasess and index

From: "Darko Prenosil" <Darko(dot)Prenosil(at)finteh(dot)hr>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Operator clasess and index
Date: 2001-12-03 20:39:39
Message-ID: 000801c17c3a$a6b54710$ef00000a@darko
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have question for someone who is familiar with indexes and index classes.

I tried to write a "begins with" operator to use it instead of LIKE 'xx%',
because LIKE 'xx%' can't use index.

These are steps I did:

1. Created function that returns true if "strInput" begins with "strMatch":

CREATE OR REPLACE FUNCTION begins_with(varchar, varchar) RETURNS boolean AS '
DECLARE strInput ALIAS FOR $1;
strMatch ALIAS FOR $2;
nLenMatch INTEGER;
nLenStr INTEGER;
BEGIN
nLenStr=char_length(strInput);
nLenMatch=char_length(strMatch);
IF nLenStr<nLenMatch THEN
RETURN false;
END IF;
IF substr(strInput,1,nLenMatch)=strMatch THEN
RETURN true;
END IF;
RETURN false;
END;' LANGUAGE 'plpgsql';

2. Created operator <==
DROP OPERATOR <== (varchar,varchar);
CREATE OPERATOR <==
( leftarg = varchar,
rightarg = varchar,
procedure = begins_with , restrict = eqsel, join = eqjoinsel );

3. Inserted record for begins_with in pg_ampop:
INSERT INTO pg_amop
(amopclaid, amopstrategy, amopreqcheck, amopopr)
SELECT
(SELECT pg_opclass.oid FROM pg_opclass WHERE opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree') AND opcname = 'varchar_ops'),
6,
false,
(SELECT o.oid FROM pg_operator o, pg_type t WHERE o.oprname = '<==' and o.oprleft = t.oid and o.oprright = t.oid and t.typname = 'varchar');

4. Created index on table:
CREATE INDEX ix_zemlje_naziv ON zemlje USING btree(naziv);

5. When I try to get plan for this :
EXPLAIN SELECT * from zemlje WHERE naziv<=='A';

I got :
Index Scan using ix_zemlje_naziv on zemlje (cost=0.00..17.07 rows=5 width=405)

It seem that everything is ok, but when I execute the query I got empty recordset.
Function begins_with returns true, I did check it, but recordset is empty.

When I skip step 3, results are fine, but operator is not using index !

Is there way to do this ?

Best regards !

Browse pgsql-general by date

  From Date Subject
Next Message Barry Lind 2001-12-03 20:40:05 Re: java stored procedures
Previous Message Dave Page 2001-12-03 20:37:30 Re: [GENERAL] ident fails on win2k host...