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';