Added index ability for isbn-issn contrib example - Mailing list 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';