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

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@uniserve.de>
    ___|__|
   (___,   )           uniserve Internet & Multimedia GmbH
   (___,   )\          Sophienweg 3
   (___,   ) \         Technologiezentrum (MIT)
    (___,_,)/ \        D-59872 Meschede Germany
        \              fon: +49 291 59100 , fax: +49 291 59102types:inet, _inet
functions:
oid    | name              | returns  | args
----------------------------------------------------------
698    | broadcast         | text     | inet
699    | host              | text     | inet
910    | inet_in           | inet     |
306921 | inr_cmp           | int4     | inrecord inrecord
306924 | inr_eq            | bool     | inrecord inrecord
306925 | inr_ge            | bool     | inrecord inrecord
306926 | inr_gt            | bool     | inrecord inrecord
306914 | inr_in            | inrecord |
306920 | inr_inet          | inet     | inrecord
306923 | inr_le            | bool     | inrecord inrecord
306922 | inr_lt            | bool     | inrecord inrecord
306927 | inr_ne            | bool     | inrecord inrecord
306915 | inr_out           | opaque   |
306937 | inrinet_eq        | bool     | inrecord inet
306938 | inrinet_ge        | bool     | inrecord inet
306939 | inrinet_gt        | bool     | inrecord inet
306936 | inrinet_le        | bool     | inrecord inet
306935 | inrinet_lt        | bool     | inrecord inet
306940 | inrinet_ne        | bool     | inrecord inet
306941 | inrinet_sub       | bool     | inrecord inet
306942 | inrinet_subeq     | bool     | inrecord inet
306943 | inrinet_sup       | bool     | inrecord inet
306944 | inrinet_supeq     | bool     | inrecord inet
697    | masklen           | int4     | inet
696    | netmask           | text     | inet
683    | network           | text     | inet
945    | network_broadcast | text     | inet
926    | network_cmp       | int4     | inet inet
920    | network_eq        | bool     | inet inet
924    | network_ge        | bool     | inet inet
923    | network_gt        | bool     | inet inet
682    | network_host      | text     | inet
922    | network_le        | bool     | inet inet
921    | network_lt        | bool     | inet inet
941    | network_masklen   | int4     | inet
925    | network_ne        | bool     | inet inet
940    | network_netmask   | text     | inet
473    | network_network   | text     | inet
927    | network_sub       | bool     | inet inet
928    | network_subeq     | bool     | inet inet
929    | network_sup       | bool     | inet inet
930    | network_supeq     | bool     | inet inet
opclass:
name     | type | typname
-------------------------
inet_ops | 869  | inet
pg_amop:
oid   | name     | type | op | proc       | strategy | select   | npages     | amname
-------------------------------------------------------------------------------------
16805 | inet_ops | inet | <  | network_lt | 1        | btreesel | btreenpage | btree
16806 | inet_ops | inet | <= | network_le | 2        | btreesel | btreenpage | btree
16842 | inet_ops | inet | =  | network_eq | 1        | hashsel  | hashnpage  | hash
16807 | inet_ops | inet | =  | network_eq | 3        | btreesel | btreenpage | btree
16809 | inet_ops | inet | >  | network_gt | 5        | btreesel | btreenpage | btree
16808 | inet_ops | inet | >= | network_ge | 4        | btreesel | btreenpage | btree
pg_amproc:
oid   | opcname  | type | proc        | amname | procnum
--------------------------------------------------------
16885 | inet_ops | inet | network_cmp | btree  | 1
types:inrecord, _inrecord
functions:
oid    | name            | returns  | args
--------------------------------------------------------
306921 | inr_cmp         | int4     | inrecord inrecord
306919 | inr_data        | text     | inrecord
306924 | inr_eq          | bool     | inrecord inrecord
306925 | inr_ge          | bool     | inrecord inrecord
306926 | inr_gt          | bool     | inrecord inrecord
306914 | inr_in          | inrecord |
306920 | inr_inet        | inet     | inrecord
306923 | inr_le          | bool     | inrecord inrecord
306964 | inr_like        | bool     | inrecord inrecord
306922 | inr_lt          | bool     | inrecord inrecord
306927 | inr_ne          | bool     | inrecord inrecord
306967 | inr_nlike       | bool     | inrecord inrecord
306918 | inr_nr          | int4     | inrecord
306973 | inr_nregexp     | bool     | inrecord inrecord
306915 | inr_out         | opaque   |
306970 | inr_regexp      | bool     | inrecord inrecord
306917 | inr_type        | bpchar   | inrecord
306937 | inrinet_eq      | bool     | inrecord inet
306938 | inrinet_ge      | bool     | inrecord inet
306939 | inrinet_gt      | bool     | inrecord inet
306936 | inrinet_le      | bool     | inrecord inet
306935 | inrinet_lt      | bool     | inrecord inet
306940 | inrinet_ne      | bool     | inrecord inet
306941 | inrinet_sub     | bool     | inrecord inet
306942 | inrinet_subeq   | bool     | inrecord inet
306943 | inrinet_sup     | bool     | inrecord inet
306944 | inrinet_supeq   | bool     | inrecord inet
306965 | inrtext_like    | bool     | inrecord text
306966 | inrtext_like    | bool     | inrecord bpchar
306968 | inrtext_nlike   | bool     | inrecord text
306969 | inrtext_nlike   | bool     | inrecord bpchar
306974 | inrtext_nregexp | bool     | inrecord text
306975 | inrtext_nregexp | bool     | inrecord bpchar
306971 | inrtext_regexp  | bool     | inrecord text
306972 | inrtext_regexp  | bool     | inrecord bpchar
opclass:
name         | type   | typname
--------------------------------
inrecord_ops | 306913 | inrecord
pg_amop:
oid    | name         | type     | op | proc   | strategy | select   | npages     | amname
------------------------------------------------------------------------------------------
307016 | inrecord_ops | inrecord | <  | inr_lt | 1        | btreesel | btreenpage | btree
307014 | inrecord_ops | inrecord | <= | inr_le | 2        | btreesel | btreenpage | btree
307017 | inrecord_ops | inrecord | =  | inr_eq | 3        | btreesel | btreenpage | btree
307018 | inrecord_ops | inrecord | >  | inr_gt | 5        | btreesel | btreenpage | btree
307015 | inrecord_ops | inrecord | >= | inr_ge | 4        | btreesel | btreenpage | btree
-----------------------------------------------------------
-- install the in_record type
-----------------------------------------------------------
create function inr_in(opaque)
    RETURNS inrecord
    AS '/home/drillich/src/dns/db/src/in_record.so'
    Language 'C';

create function inr_out(opaque)
    RETURNS opaque
    AS '/home/drillich/src/dns/db/src/in_record.so'
    Language 'C';

create type inrecord (
    internallength=VARIABLE,
    input=inr_in,
    output=inr_out
);

-----------------------------------------------------------
-- return type as text
create function inr_type(inrecord)
    returns bpchar
    as '/home/drillich/src/dns/db/src/in_record.so'
    Language 'C';
-- or number
create function inr_nr(inrecord)
    returns int
    as '/home/drillich/src/dns/db/src/in_record.so'
    Language 'C';
-----------------------------------------------------------
--
-- return record as text
--
create function inr_data(inrecord)
    returns text
    as '/home/drillich/src/dns/db/src/in_record.so'
    Language 'C';
--
-- inr_inet:
--     return address record as inet
--    drops error on text record
create function inr_inet(inrecord)
    returns inet
    as '/home/drillich/src/dns/db/src/in_record.so'
    Language 'C';
-----------------------------------------------------------
--
-- comparision functions
--    inrecord,inrecord
create function inr_cmp(inrecord,inrecord)
    returns int4
    as '/home/drillich/src/dns/db/src/in_record.so'
    Language 'C';
create function inr_lt(inrecord,inrecord)
    returns bool
    as '/home/drillich/src/dns/db/src/in_record.so'
    Language 'C';
create function inr_le(inrecord,inrecord)
    returns bool
    as '/home/drillich/src/dns/db/src/in_record.so'
    Language 'C';
create function inr_eq(inrecord,inrecord)
    returns bool
    as '/home/drillich/src/dns/db/src/in_record.so'
    Language 'C';
create function inr_ge(inrecord,inrecord)
    returns bool
    as '/home/drillich/src/dns/db/src/in_record.so'
    Language 'C';
create function inr_gt(inrecord,inrecord)
    returns bool
    as '/home/drillich/src/dns/db/src/in_record.so'
    Language 'C';
create function inr_ne(inrecord,inrecord)
    returns bool
    as '/home/drillich/src/dns/db/src/in_record.so'
    Language 'C';

--
-- comparision operators
--    inrecord,inrecord
create operator < (
    procedure=inr_lt,
    leftarg=inrecord,
    rightarg=inrecord,
    commutator= <,
    negator= >=
);
create operator <= (
    procedure=inr_le,
    leftarg=inrecord,
    rightarg=inrecord,
    commutator= <=,
    negator= >
);
create operator = (
    procedure=inr_eq,
    leftarg=inrecord,
    rightarg=inrecord,
    commutator= '=',
    negator= '!=',
    restrict = eqsel,
    join = eqjoinsel,
    HASHES
);
create operator >= (
    procedure=inr_ge,
    leftarg=inrecord,
    rightarg=inrecord,
    commutator= =,
    negator= <
);
create operator > (
    procedure=inr_gt,
    leftarg=inrecord,
    rightarg=inrecord,
    commutator= >,
    negator= <=
);
create operator != (
    procedure=inr_ne,
    leftarg=inrecord,
    rightarg=inrecord,
    commutator= '!=',
    negator= '=',
    restrict = neqsel,
    join = neqjoinsel,
    HASHES
);

-----------------------------------------------------------
--
-- comparision functions
--    inrecord,inet
--    inrinet_lt    <
--    inrinet_le    <=
--    inrinet_eq    =
--    inrinet_ge    >=
--    inrinet_gt    >
--    inrinet_sub    <<
--    inrinet_subeq    <<=
--    inrinet_sup    >>
--    inrinet_supeq    >>=

create function inrinet_lt(inrecord,inet)
    returns bool
    as '/home/drillich/src/dns/db/src/in_record.so'
    Language 'C';
create function inrinet_le(inrecord,inet)
    returns bool
    as '/home/drillich/src/dns/db/src/in_record.so'
    Language 'C';
create function inrinet_eq(inrecord,inet)
    returns bool
    as '/home/drillich/src/dns/db/src/in_record.so'
    Language 'C';
create function inrinet_ge(inrecord,inet)
    returns bool
    as '/home/drillich/src/dns/db/src/in_record.so'
    Language 'C';
create function inrinet_gt(inrecord,inet)
    returns bool
    as '/home/drillich/src/dns/db/src/in_record.so'
    Language 'C';
create function inrinet_ne(inrecord,inet)
    returns bool
    as '/home/drillich/src/dns/db/src/in_record.so'
    Language 'C';
create function inrinet_sub(inrecord,inet)
    returns bool
    as '/home/drillich/src/dns/db/src/in_record.so'
    Language 'C';
create function inrinet_subeq(inrecord,inet)
    returns bool
    as '/home/drillich/src/dns/db/src/in_record.so'
    Language 'C';
create function inrinet_sup(inrecord,inet)
    returns bool
    as '/home/drillich/src/dns/db/src/in_record.so'
    Language 'C';
create function inrinet_supeq(inrecord,inet)
    returns bool
    as '/home/drillich/src/dns/db/src/in_record.so'
    Language 'C';

--
-- comparision operators
--    inrecord,inrecord
create operator < (
    procedure=inrinet_lt,
    leftarg=inrecord,
    rightarg=inet,
    commutator= <,
    negator= >=
);
create operator <= (
    procedure=inrinet_le,
    leftarg=inrecord,
    rightarg=inet,
    commutator= <=,
    negator= >
);
create operator = (
    procedure=inrinet_eq,
    leftarg=inrecord,
    rightarg=inet,
    commutator= =,
    negator= !=
);
create operator >= (
    procedure=inrinet_ge,
    leftarg=inrecord,
    rightarg=inet,
    commutator= >=,
    negator= <
);
create operator > (
    procedure=inrinet_gt,
    leftarg=inrecord,
    rightarg=inet,
    commutator= >,
    negator= <=
);
create operator << (
    procedure=inrinet_sub,
    leftarg=inrecord,
    rightarg=inet,
    commutator= <<
);
create operator <<= (
    procedure=inrinet_subeq,
    leftarg=inrecord,
    rightarg=inet,
    commutator= <<=
);
create operator >> (
    procedure=inrinet_sup,
    leftarg=inrecord,
    rightarg=inet,
    commutator= >>
);
create operator >>= (
    procedure=inrinet_supeq,
    leftarg=inrecord,
    rightarg=inet,
    commutator= >>=
);
---------------------------------------
--
-- comparision functions for cnames
--    (case insensitive)
--    inrecord,inrecord
--        inr_like    ~~
--        inr_nlike    !~~
--        inr_regexp    ~
--        inr_nregexp    !~
--    inrecord,text
--        inrtext_like    ~~
--        inrtext_nlike    !~~
--        inrtext_regexp    ~
--        inrtext_nregexp    !~
create function inr_like(inrecord,inrecord)
    returns bool
    as '/home/drillich/src/dns/db/src/in_record.so'
    Language 'C';
create function inrtext_like(inrecord,text)
    returns bool
    as '/home/drillich/src/dns/db/src/in_record.so'
    Language 'C';
create function inrtext_like(inrecord,bpchar)
    returns bool
    as '/home/drillich/src/dns/db/src/in_record.so'
    Language 'C';

create function inr_nlike(inrecord,inrecord)
    returns bool
    as '/home/drillich/src/dns/db/src/in_record.so'
    Language 'C';
create function inrtext_nlike(inrecord,text)
    returns bool
    as '/home/drillich/src/dns/db/src/in_record.so'
    Language 'C';
create function inrtext_nlike(inrecord,bpchar)
    returns bool
    as '/home/drillich/src/dns/db/src/in_record.so'
    Language 'C';

create function inr_regexp(inrecord,inrecord)
    returns bool
    as '/home/drillich/src/dns/db/src/in_record.so'
    Language 'C';
create function inrtext_regexp(inrecord,text)
    returns bool
    as '/home/drillich/src/dns/db/src/in_record.so'
    Language 'C';
create function inrtext_regexp(inrecord,bpchar)
    returns bool
    as '/home/drillich/src/dns/db/src/in_record.so'
    Language 'C';

create function inr_nregexp(inrecord,inrecord)
    returns bool
    as '/home/drillich/src/dns/db/src/in_record.so'
    Language 'C';
create function inrtext_nregexp(inrecord,text)
    returns bool
    as '/home/drillich/src/dns/db/src/in_record.so'
    Language 'C';
create function inrtext_nregexp(inrecord,bpchar)
    returns bool
    as '/home/drillich/src/dns/db/src/in_record.so'
    Language 'C';
-- operators
create operator ~~ (
    procedure=inr_like,
    leftarg=inrecord,
    rightarg=inrecord,
    commutator= ~~,
    negator= !~~
);
create operator ~~ (
    procedure=inrtext_like,
    leftarg=inrecord,
    rightarg=text,
    commutator= ~~,
    negator= !~~
);
create operator ~~ (
    procedure=inrtext_like,
    leftarg=inrecord,
    rightarg=bpchar,
    commutator= ~~,
    negator= !~~
);
create operator !~~ (
    procedure=inr_nlike,
    leftarg=inrecord,
    rightarg=inrecord,
    commutator= ~~,
    negator= !~~
);
create operator !~~ (
    procedure=inrtext_nlike,
    leftarg=inrecord,
    rightarg=text,
    commutator= !~~,
    negator= ~~
);
create operator !~~ (
    procedure=inrtext_nlike,
    leftarg=inrecord,
    rightarg=bpchar,
    commutator= !~~,
    negator= ~~
);
create operator ~ (
    procedure=inr_regexp,
    leftarg=inrecord,
    rightarg=inrecord,
    commutator= ~,
    negator= !~
);
create operator ~ (
    procedure=inrtext_regexp,
    leftarg=inrecord,
    rightarg=text,
    commutator= ~,
    negator= !~
);
create operator ~ (
    procedure=inrtext_regexp,
    leftarg=inrecord,
    rightarg=bpchar,
    commutator= ~,
    negator= !~
);
create operator !~ (
    procedure=inr_nregexp,
    leftarg=inrecord,
    rightarg=inrecord,
    commutator= !~,
    negator= ~
);
create operator !~ (
    procedure=inrtext_nregexp,
    leftarg=inrecord,
    rightarg=text,
    commutator= !~,
    negator= ~
);
create operator !~ (
    procedure=inrtext_nregexp,
    leftarg=inrecord,
    rightarg=bpchar,
    commutator= !~,
    negator= ~
);
-- EOF in_record.sql