Re: [GENERAL] no primary key on self designed type - Mailing list pgsql-general

> 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