pg_typemgr ; -) [was Re: [GENERAL] no primary key on self designed type]

Hi Gene,

is this correct

pg_typemgr.html # description of my perl-program

pg_typemgr -p inr -d test inrecord # delete type
pg_typemgr -p inr -f in_record.sql test inrecord # delete and restore
pg_typemgr -p inr -l all test inrecord > inrecord.out # list anything
pg_typemgr -p inr -l all test inet > inet.out # to compare

When I have to setup pg_amproc like in inet.out ?
Whats about inet | = | hashsel | hashnpage | hash ..

thanks in advance
tom.

"Gene Selkov Jr." wrote:
>
> > 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

--
mit freundlichem Gruss -- regards
,-,
| | Thomas Drillich <drillich(at)uniserve(dot)de>
___|__|
(___, ) uniserve Internet & Multimedia GmbH
(___, )\ Sophienweg 3
(___, ) \ Technologiezentrum (MIT)
(___,_,)/ \ D-59872 Meschede Germany
\ fon: +49 291 59100 , fax: +49 291 59102