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 <[EMAIL PROTECTED]>
___|__|
(___, ) uniserve Internet & Multimedia GmbH
(___, )\ Sophienweg 3
(___, ) \ Technologiezentrum (MIT)
(___,_,)/ \ D-59872 Meschede Germany
\ fon: +49 291 59100 , fax: +49 291 59102
Title: pg_typemgr
- NAME pg_typemgr [Options] <dbname> <type> [|<type>[,<prefix>]]
- Description
- Options
- Placeholders
- Bugs
- Todo
- See Also
- Copyright
- Disclaimer
NAME pg_typemgr [Options] <dbname> <type> [|<type>[,<prefix>]]
Description
pg_typemgr trys to manage self designed types for postgreSQL.
Options
- -h <host> | --host=<host>
-
set the hostname where the postmaster runs, default `localhost'.
- -u <user> | --user=<user>
-
connect to database as <user>.
- --pass=<password>
-
use <password> on connect.
- -d | --delete
-
delete <type>, you have to backup the tables before.
- -f <file> | --file=<file>
-
sql command file to redefine <type>.
- -l <what> | --l<what>
-
list one of
- -p <prefix> | --prefix=<prefix>
-
prefix of functions which should include in the query, eg. <prefix>_cmp>, on default <prefix> == <type>
- --lt=<ltfunction>
-
the function for the operator `<' default bool
_lt(<type>*,<type> *)>.- --le=<lefunction>
the function for the operator `<='
- --eq=<eqfunction>
the function for the operator `='
- --ge=<gefunction>
the function for the operator `>='
- --gt=<gtfunction>
the function for the operator `>'
- --ne=<nefunction>
the function for the operator `!='
- --cmp=<cmpfunction>
function which returns (-1,0,1) on (a<b,a==b,a>b) the compare function, default
int4 <prefix>_cmp(<type> *,<type> *)
Placeholders
- <dbname>
-
the name of the database where pg_typemgr operates on.
- <type>
-
the name of the data type.
- <prefix>
-
the prefix of functions which should include in the query, default <type>.
- <host>
-
the hostname of the postgreSQL server.
Bugs
this is the alpha version, need testing ;-)
Todo
correct update pg_amproc on insert of <type>.
See Also
Copyright
(c) 1999-2002 Thomas Drillich <[EMAIL PROTECTED]>
Disclaimer
Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.
IN NO EVENT SHALL THE AUTHOR OR HIS COMPANY BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
THE THE AUTHOR OR HIS COMPANY SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN ``AS IS'' BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
types: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
