From: | Dan Weston <ddweston(at)cinesite(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Added index ability for isbn-issn contrib example |
Date: | 2002-05-21 01:45:42 |
Message-ID: | Pine.LNX.4.33.0205201835490.28077-100000@musk.hollywood.cinesite.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I tried to use the isbn data type created in the
/usr/lib/pgsql/contrib/isbn_issn directory, but couldn't make isbn a
primary key (it gave me an error message about no default operator class).
A quick search of the Programmer's Guide gave me what I needed below,
allowing the creation of a btree index. You might want the following to
add to the file isbn_issn.sql twice, once for 'isbn' and once more
changing 'isbn' to 'issn':
-------------------------------------------------
-- Create default operator class for 'isbn' --
-- Needed to create index or primary key --
-------------------------------------------------
-- Register new operator class with system catalog pg_opclass
insert into pg_opclass
(opcamid, opcname, opcintype, opcdefault, opckeytype)
values ((select oid from pg_am where amname = 'btree'),
'isbn_ops',
(select oid from pg_type where typname = 'isbn'),
true,
0);
-- Verify that new operator class was added to pg_opclass
-- select oid,* from pg_opclass where opcname = 'isbn_ops';
-- Identify comparison operators for 'isbn' type
select o.oid as opoid, o.oprname
into temp table isbn_ops_tmp
from pg_operator o, pg_type t
where o.oprleft = t.oid
and o.oprright = t.oid
and t.typname = 'isbn';
-- Make sure all 5 needed order ops are there (<, <=, =, >=, >)
-- Operator <> will be present but is not needed
-- select * from isbn_ops_tmp order by opoid;
-- Associate B-tree strategy 1 with <
insert into pg_amop
(amopclaid, amopstrategy, amopreqcheck, amopopr)
select opcl.oid, 1, false, c.opoid
from pg_opclass opcl, isbn_ops_tmp c
where opcamid = (select oid from pg_am where amname = 'btree')
and opcname = 'isbn_ops'
and c.oprname = '<';
-- Associate B-tree strategy 2 with <=
insert into pg_amop
(amopclaid, amopstrategy, amopreqcheck, amopopr)
select opcl.oid, 2, false, c.opoid
from pg_opclass opcl, isbn_ops_tmp c
where opcamid = (select oid from pg_am where amname = 'btree')
and opcname = 'isbn_ops'
and c.oprname = '<=';
-- Associate B-tree strategy 3 with =
insert into pg_amop
(amopclaid, amopstrategy, amopreqcheck, amopopr)
select opcl.oid, 3, false, c.opoid
from pg_opclass opcl, isbn_ops_tmp c
where opcamid = (select oid from pg_am where amname = 'btree')
and opcname = 'isbn_ops'
and c.oprname = '=';
-- Associate B-tree strategy 4 with >=
insert into pg_amop
(amopclaid, amopstrategy, amopreqcheck, amopopr)
select opcl.oid, 4, false, c.opoid
from pg_opclass opcl, isbn_ops_tmp c
where opcamid = (select oid from pg_am where amname = 'btree')
and opcname = 'isbn_ops'
and c.oprname = '>=';
-- Associate B-tree strategy 5 with >
insert into pg_amop
(amopclaid, amopstrategy, amopreqcheck, amopopr)
select opcl.oid, 5, false, c.opoid
from pg_opclass opcl, isbn_ops_tmp c
where opcamid = (select oid from pg_am where amname = 'btree')
and opcname = 'isbn_ops'
and c.oprname = '>';
-- Register 'isbn' comparison function
create function isbn_cmp(isbn, isbn)
returns integer
as '$libdir/isbn_issn'
language c;
-- Make sure that function was correctly registered
-- select oid, proname from pg_proc where proname = 'isbn_cmp';
-- Associate default btree operator class with 'isbn' comparison function
insert into pg_amproc
(amopclaid, amprocnum, amproc)
select opcl.oid, 1, p.oid
from pg_opclass opcl, pg_proc p
where opcamid = (select oid from pg_am where amname = 'btree')
and opcname = 'isbn_ops'
and p.proname = 'isbn_cmp';
From | Date | Subject | |
---|---|---|---|
Next Message | Steve Lane | 2002-05-21 01:59:32 | Re: MacOS X Shared Buffers (SHMMAX)? |
Previous Message | Neil Conway | 2002-05-21 01:00:43 | Re: Help.. |