Re: [GENERAL] no primary key on self designed type

> Hello,
>
> create type inrecord (
> internallength=VARIABLE,
> input=inr_in,
> output=inr_out
> );
>
> create table test (
> data inrecord not null primary key
> );
> ... result ...
> ERROR: Can't find a default operator class for type 268128.
>
> how can I define the default operator class ??
> --

The short answer is,

INSERT INTO pg_opclass (opcname, opcdeftype)
SELECT 'inrecord_ops', oid
FROM pg_type
WHERE typname = 'inrecord';

But you won't get away with just that. You probably want a non-empty
opclass. For example, if your type, inrecord, needs a btree opclass,
you'll want to do:

SELECT o.oid AS opoid, o.oprname
INTO TABLE inrecord_ops_tmp
FROM pg_operator o, pg_type t
WHERE o.oprleft = t.oid and o.oprright = t.oid
and t.typname = 'inrecord';

INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy,
amopselect, amopnpages)
SELECT am.oid, opcl.oid, c.opoid, 1,
'btreesel'::regproc, 'btreenpage'::regproc
FROM pg_am am, pg_opclass opcl, inrecord_ops_tmp c
WHERE amname = 'btree' and opcname = 'inrecord_ops'
and c.oprname = '<';

INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy,
amopselect, amopnpages)
SELECT am.oid, opcl.oid, c.opoid, 2,
'btreesel'::regproc, 'btreenpage'::regproc
FROM pg_am am, pg_opclass opcl, inrecord_ops_tmp c
WHERE amname = 'btree' and opcname = 'inrecord_ops'
and c.oprname = '<=';

INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy,
amopselect, amopnpages)
SELECT am.oid, opcl.oid, c.opoid, 3,
'btreesel'::regproc, 'btreenpage'::regproc
FROM pg_am am, pg_opclass opcl, inrecord_ops_tmp c
WHERE amname = 'btree' and opcname = 'inrecord_ops'
and c.oprname = '=';

INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy,
amopselect, amopnpages)
SELECT am.oid, opcl.oid, c.opoid, 4,
'btreesel'::regproc, 'btreenpage'::regproc
FROM pg_am am, pg_opclass opcl, inrecord_ops_tmp c
WHERE amname = 'btree' and opcname = 'inrecord_ops'
and c.oprname = '>=';

INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy,
amopselect, amopnpages)
SELECT am.oid, opcl.oid, c.opoid, 5,
'btreesel'::regproc, 'btreenpage'::regproc
FROM pg_am am, pg_opclass opcl, inrecord_ops_tmp c
WHERE amname = 'btree' and opcname = 'inrecord_ops'
and c.oprname = '>';

DROP table inrecord_ops_tmp;

Which isn't all yet. The code above assumes that you have defined the
operators, '=', '>=', etc.:

CREATE OPERATOR = (
leftarg = inrecord, rightarg = inrecord_code, procedure = inrecord_eq,
restrict = eqsel, join = eqjoinsel
);

If that didn't make you sick already, you also need to define the
procedures, such as inrecord_eq in this example, and possibly write
some c code for them:

CREATE FUNCTION inrecord_eq(inrecord, inrecord) RETURNS bool
AS '${LIBDIR}/inrecord.so' LANGUAGE 'c';

INSERT INTO pg_description (objoid, description)
SELECT oid, 'equals'::text
FROM pg_proc
WHERE proname = 'inrecord_eq'::name;

Thar's, in short, what is required to build a completely new type. One
might as well attempt to borrow some code or the whole opclass from
existing similar types, but I would hesitate to even consider doing that
without the thorough knowledge of the current postgres schema, which
is unfortunately not yet covered by the contemporary docs.

--Gene