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