diff options
Diffstat (limited to 'contrib')
| -rw-r--r-- | contrib/btree_gist/btree_gist.sql.in | 223 | ||||
| -rw-r--r-- | contrib/cube/cube.sql.in | 180 | ||||
| -rw-r--r-- | contrib/isbn_issn/isbn_issn.sql.in | 201 | ||||
| -rw-r--r-- | contrib/rtree_gist/rtree_gist.sql.in | 343 | ||||
| -rw-r--r-- | contrib/seg/seg.sql.in | 180 | ||||
| -rw-r--r-- | contrib/tsearch/tsearch.sql.in | 108 |
6 files changed, 170 insertions, 1065 deletions
diff --git a/contrib/btree_gist/btree_gist.sql.in b/contrib/btree_gist/btree_gist.sql.in index 98b868afc72..dbb68c7c748 100644 --- a/contrib/btree_gist/btree_gist.sql.in +++ b/contrib/btree_gist/btree_gist.sql.in @@ -1,6 +1,9 @@ begin transaction; --- create type of int4 key +-- Adjust this setting to control where the objects get created. +SET search_path = public; + +-- create type of int4 key CREATE FUNCTION int4key_in(opaque) RETURNS opaque AS 'MODULE_PATHNAME' @@ -40,101 +43,23 @@ create function gint4_union(bytea, opaque) returns int4 as 'MODULE_PATHNAME' lan create function gint4_same(opaque, opaque, opaque) returns opaque as 'MODULE_PATHNAME' language 'C'; --- add a new opclass -INSERT INTO pg_opclass (opcamid, opcname, opcnamespace, opcowner, opcintype, opcdefault, opckeytype) - VALUES ( - (SELECT oid FROM pg_am WHERE amname = 'gist'), - 'gist_int4_ops', - (SELECT oid FROM pg_namespace WHERE nspname = 'pg_catalog'), - 1, -- UID of superuser is hardwired to 1 as of PG 7.3 - (SELECT oid FROM pg_type WHERE typname = 'int4'), - true, - (SELECT oid FROM pg_type WHERE typname = 'int4key')); - - -SELECT o.oid AS opoid, o.oprname -INTO TABLE int_ops_tmp -FROM pg_operator o, pg_type t -WHERE o.oprleft = t.oid and o.oprright = t.oid - and t.typname = 'int4'; - --- get the comparators for int4es and store them in a tmp table -INSERT INTO pg_amop (amopclaid, amopopr, amopstrategy, amopreqcheck) - SELECT opcl.oid, c.opoid, 1, 'f' - FROM pg_opclass opcl, int_ops_tmp c - WHERE opcname = 'gist_int4_ops' - and c.oprname = '<'; - -INSERT INTO pg_amop (amopclaid, amopopr, amopstrategy, amopreqcheck) - SELECT opcl.oid, c.opoid, 2, 'f' - FROM pg_opclass opcl, int_ops_tmp c - WHERE opcname = 'gist_int4_ops' - and c.oprname = '<='; - -INSERT INTO pg_amop (amopclaid, amopopr, amopstrategy, amopreqcheck) - SELECT opcl.oid, c.opoid, 3, 'f' - FROM pg_opclass opcl, int_ops_tmp c - WHERE opcname = 'gist_int4_ops' - and c.oprname = '='; - -INSERT INTO pg_amop (amopclaid, amopopr, amopstrategy, amopreqcheck) - SELECT opcl.oid, c.opoid, 4, 'f' - FROM pg_opclass opcl, int_ops_tmp c - WHERE opcname = 'gist_int4_ops' - and c.oprname = '>='; - -INSERT INTO pg_amop (amopclaid, amopopr, amopstrategy, amopreqcheck) - SELECT opcl.oid, c.opoid, 5, 'f' - FROM pg_opclass opcl, int_ops_tmp c - WHERE opcname = 'gist_int4_ops' - and c.oprname = '>'; - - -DROP table int_ops_tmp; - --- add the entries to amproc for the support methods --- note the amprocnum numbers associated with each are specific! -INSERT INTO pg_amproc (amopclaid, amproc, amprocnum) - SELECT opcl.oid, pro.oid, 1 - FROM pg_opclass opcl, pg_proc pro - WHERE opcname = 'gist_int4_ops' - and proname = 'gint4_consistent'; - -INSERT INTO pg_amproc (amopclaid, amproc, amprocnum) - SELECT opcl.oid, pro.oid, 2 - FROM pg_opclass opcl, pg_proc pro - WHERE opcname = 'gist_int4_ops' - and proname = 'gint4_union'; - -INSERT INTO pg_amproc (amopclaid, amproc, amprocnum) - SELECT opcl.oid, pro.oid, 3 - FROM pg_opclass opcl, pg_proc pro - WHERE opcname = 'gist_int4_ops' - and proname = 'gint4_compress'; - -INSERT INTO pg_amproc (amopclaid, amproc, amprocnum) - SELECT opcl.oid, pro.oid, 4 - FROM pg_opclass opcl, pg_proc pro - WHERE opcname = 'gist_int4_ops' - and proname = 'btree_decompress'; +-- Create the operator class +CREATE OPERATOR CLASS gist_int4_ops + DEFAULT FOR TYPE int4 USING gist AS + OPERATOR 1 < , + OPERATOR 2 <= , + OPERATOR 3 = , + OPERATOR 4 >= , + OPERATOR 5 > , + FUNCTION 1 gint4_consistent (opaque, int4, int2), + FUNCTION 2 gint4_union (bytea, opaque), + FUNCTION 3 gint4_compress (opaque), + FUNCTION 4 btree_decompress (opaque), + FUNCTION 5 gint4_penalty (opaque, opaque, opaque), + FUNCTION 6 gint4_picksplit (opaque, opaque), + FUNCTION 7 gint4_same (opaque, opaque, opaque), + STORAGE int4key; -INSERT INTO pg_amproc (amopclaid, amproc, amprocnum) - SELECT opcl.oid, pro.oid, 5 - FROM pg_opclass opcl, pg_proc pro - WHERE opcname = 'gist_int4_ops' - and proname = 'gint4_penalty'; - -INSERT INTO pg_amproc (amopclaid, amproc, amprocnum) - SELECT opcl.oid, pro.oid, 6 - FROM pg_opclass opcl, pg_proc pro - WHERE opcname = 'gist_int4_ops' - and proname = 'gint4_picksplit'; - -INSERT INTO pg_amproc (amopclaid, amproc, amprocnum) - SELECT opcl.oid, pro.oid, 7 - FROM pg_opclass opcl, pg_proc pro - WHERE opcname = 'gist_int4_ops' - and proname = 'gint4_same'; -- -- @@ -173,98 +98,22 @@ create function gts_union(bytea, opaque) returns int4 as 'MODULE_PATHNAME' langu create function gts_same(opaque, opaque, opaque) returns opaque as 'MODULE_PATHNAME' language 'C'; --- add a new opclass -INSERT INTO pg_opclass (opcamid, opcname, opcnamespace, opcowner, opcintype, opcdefault, opckeytype) - VALUES ( - (SELECT oid FROM pg_am WHERE amname = 'gist'), - 'gist_timestamp_ops', - (SELECT oid FROM pg_namespace WHERE nspname = 'pg_catalog'), - 1, -- UID of superuser is hardwired to 1 as of PG 7.3 - (SELECT oid FROM pg_type WHERE typname = 'timestamp'), - true, - (SELECT oid FROM pg_type WHERE typname = 'tskey')); - -SELECT o.oid AS opoid, o.oprname -INTO TABLE timestamp_ops_tmp -FROM pg_operator o, pg_type t -WHERE o.oprleft = t.oid and o.oprright = t.oid - and t.typname = 'timestamp'; - -INSERT INTO pg_amop (amopclaid, amopopr, amopstrategy, amopreqcheck) - SELECT opcl.oid, c.opoid, 1, 'f' - FROM pg_opclass opcl, timestamp_ops_tmp c - WHERE opcname = 'gist_timestamp_ops' - and c.oprname = '<'; - -INSERT INTO pg_amop (amopclaid, amopopr, amopstrategy, amopreqcheck) - SELECT opcl.oid, c.opoid, 2, 'f' - FROM pg_opclass opcl, timestamp_ops_tmp c - WHERE opcname = 'gist_timestamp_ops' - and c.oprname = '<='; - -INSERT INTO pg_amop (amopclaid, amopopr, amopstrategy, amopreqcheck) - SELECT opcl.oid, c.opoid, 3, 'f' - FROM pg_opclass opcl, timestamp_ops_tmp c - WHERE opcname = 'gist_timestamp_ops' - and c.oprname = '='; - -INSERT INTO pg_amop (amopclaid, amopopr, amopstrategy, amopreqcheck) - SELECT opcl.oid, c.opoid, 4, 'f' - FROM pg_opclass opcl, timestamp_ops_tmp c - WHERE opcname = 'gist_timestamp_ops' - and c.oprname = '>='; - -INSERT INTO pg_amop (amopclaid, amopopr, amopstrategy, amopreqcheck) - SELECT opcl.oid, c.opoid, 5, 'f' - FROM pg_opclass opcl, timestamp_ops_tmp c - WHERE opcname = 'gist_timestamp_ops' - and c.oprname = '>'; +-- Create the operator class +CREATE OPERATOR CLASS gist_timestamp_ops + DEFAULT FOR TYPE timestamp USING gist AS + OPERATOR 1 < , + OPERATOR 2 <= , + OPERATOR 3 = , + OPERATOR 4 >= , + OPERATOR 5 > , + FUNCTION 1 gts_consistent (opaque, timestamp, int2), + FUNCTION 2 gts_union (bytea, opaque), + FUNCTION 3 gts_compress (opaque), + FUNCTION 4 btree_decompress (opaque), + FUNCTION 5 gts_penalty (opaque, opaque, opaque), + FUNCTION 6 gts_picksplit (opaque, opaque), + FUNCTION 7 gts_same (opaque, opaque, opaque), + STORAGE tskey; -DROP table timestamp_ops_tmp; - --- add the entries to amproc for the support methods --- note the amprocnum numbers associated with each are specific! -INSERT INTO pg_amproc (amopclaid, amproc, amprocnum) - SELECT opcl.oid, pro.oid, 1 - FROM pg_opclass opcl, pg_proc pro - WHERE opcname = 'gist_timestamp_ops' - and proname = 'gts_consistent'; - -INSERT INTO pg_amproc (amopclaid, amproc, amprocnum) - SELECT opcl.oid, pro.oid, 2 - FROM pg_opclass opcl, pg_proc pro - WHERE opcname = 'gist_timestamp_ops' - and proname = 'gts_union'; - -INSERT INTO pg_amproc (amopclaid, amproc, amprocnum) - SELECT opcl.oid, pro.oid, 3 - FROM pg_opclass opcl, pg_proc pro - WHERE opcname = 'gist_timestamp_ops' - and proname = 'gts_compress'; - -INSERT INTO pg_amproc (amopclaid, amproc, amprocnum) - SELECT opcl.oid, pro.oid, 4 - FROM pg_opclass opcl, pg_proc pro - WHERE opcname = 'gist_timestamp_ops' - and proname = 'btree_decompress'; - -INSERT INTO pg_amproc (amopclaid, amproc, amprocnum) - SELECT opcl.oid, pro.oid, 5 - FROM pg_opclass opcl, pg_proc pro - WHERE opcname = 'gist_timestamp_ops' - and proname = 'gts_penalty'; - -INSERT INTO pg_amproc (amopclaid, amproc, amprocnum) - SELECT opcl.oid, pro.oid, 6 - FROM pg_opclass opcl, pg_proc pro - WHERE opcname = 'gist_timestamp_ops' - and proname = 'gts_picksplit'; - -INSERT INTO pg_amproc (amopclaid, amproc, amprocnum) - SELECT opcl.oid, pro.oid, 7 - FROM pg_opclass opcl, pg_proc pro - WHERE opcname = 'gist_timestamp_ops' - and proname = 'gts_same'; end transaction; - diff --git a/contrib/cube/cube.sql.in b/contrib/cube/cube.sql.in index a1a78ca9f58..66993e4cb69 100644 --- a/contrib/cube/cube.sql.in +++ b/contrib/cube/cube.sql.in @@ -2,6 +2,9 @@ -- BEGIN TRANSACTION; +-- Adjust this setting to control where the objects get created. +SET search_path = public; + CREATE FUNCTION cube_in(opaque) RETURNS opaque AS 'MODULE_PATHNAME' @@ -211,162 +214,25 @@ CREATE FUNCTION g_cube_same(cube, cube, opaque) RETURNS opaque AS 'MODULE_PATHNAME' LANGUAGE 'c'; --- register the default opclass for indexing -INSERT INTO pg_opclass (opcamid, opcname, opcnamespace, opcowner, opcintype, opcdefault, opckeytype) - VALUES ( - (SELECT oid FROM pg_am WHERE amname = 'gist'), - 'gist_cube_ops', - (SELECT oid FROM pg_namespace WHERE nspname = 'pg_catalog'), - 1, -- UID of superuser is hardwired to 1 as of PG 7.3 - (SELECT oid FROM pg_type WHERE typname = 'cube'), - true, - 0); - - --- get the comparators for boxes and store them in a tmp table -SELECT o.oid AS opoid, o.oprname -INTO TEMP TABLE gist_cube_ops_tmp -FROM pg_operator o, pg_type t -WHERE o.oprleft = t.oid and o.oprright = t.oid - and t.typname = 'cube'; - --- make sure we have the right operators --- SELECT * from gist_cube_ops_tmp; - --- using the tmp table, generate the amop entries - --- cube_left -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 1, false, c.opoid - FROM pg_opclass opcl, gist_cube_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_cube_ops' - and c.oprname = '<<'; - --- cube_over_left -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 2, false, c.opoid - FROM pg_opclass opcl, gist_cube_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_cube_ops' - and c.oprname = '&<'; - --- cube_overlap -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 3, false, c.opoid - FROM pg_opclass opcl, gist_cube_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_cube_ops' - and c.oprname = '&&'; - --- cube_over_right -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 4, false, c.opoid - FROM pg_opclass opcl, gist_cube_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_cube_ops' - and c.oprname = '&>'; - --- cube_right -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 5, false, c.opoid - FROM pg_opclass opcl, gist_cube_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_cube_ops' - and c.oprname = '>>'; - --- cube_same -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 6, false, c.opoid - FROM pg_opclass opcl, gist_cube_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_cube_ops' - and c.oprname = '='; - --- cube_contains -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 7, false, c.opoid - FROM pg_opclass opcl, gist_cube_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_cube_ops' - and c.oprname = '@'; - --- cube_contained -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 8, false, c.opoid - FROM pg_opclass opcl, gist_cube_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_cube_ops' - and c.oprname = '~'; - -DROP TABLE gist_cube_ops_tmp; - - --- add the entries to amproc for the support methods --- note the amprocnum numbers associated with each are specific! - -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 1, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_cube_ops' - and proname = 'g_cube_consistent'; - -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 2, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_cube_ops' - and proname = 'g_cube_union'; - -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 3, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_cube_ops' - and proname = 'g_cube_compress'; - -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 4, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_cube_ops' - and proname = 'g_cube_decompress'; - -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 5, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_cube_ops' - and proname = 'g_cube_penalty'; - -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 6, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_cube_ops' - and proname = 'g_cube_picksplit'; - -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 7, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_cube_ops' - and proname = 'g_cube_same'; +-- Create the operator class for indexing + +CREATE OPERATOR CLASS gist_cube_ops + DEFAULT FOR TYPE cube USING gist AS + OPERATOR 1 << , + OPERATOR 2 &< , + OPERATOR 3 && , + OPERATOR 4 &> , + OPERATOR 5 >> , + OPERATOR 6 = , + OPERATOR 7 @ , + OPERATOR 8 ~ , + FUNCTION 1 g_cube_consistent (opaque, cube, int4), + FUNCTION 2 g_cube_union (bytea, opaque), + FUNCTION 3 g_cube_compress (opaque), + FUNCTION 4 g_cube_decompress (opaque), + FUNCTION 5 g_cube_penalty (opaque, opaque, opaque), + FUNCTION 6 g_cube_picksplit (opaque, opaque), + FUNCTION 7 g_cube_same (cube, cube, opaque); + END TRANSACTION; diff --git a/contrib/isbn_issn/isbn_issn.sql.in b/contrib/isbn_issn/isbn_issn.sql.in index 1e9201cdeac..3a47621c2ca 100644 --- a/contrib/isbn_issn/isbn_issn.sql.in +++ b/contrib/isbn_issn/isbn_issn.sql.in @@ -1,9 +1,12 @@ -- -- PostgreSQL code for ISSNs. -- --- $Id: isbn_issn.sql.in,v 1.4 2002/07/16 00:48:30 momjian Exp $ +-- $Id: isbn_issn.sql.in,v 1.5 2002/07/30 17:34:37 tgl Exp $ -- +-- Adjust this setting to control where the objects get created. +SET search_path = public; + -- -- Input and output functions and the type itself: @@ -120,7 +123,7 @@ create operator <> ( -- -- PostgreSQL code for ISBNs. -- --- $Id: isbn_issn.sql.in,v 1.4 2002/07/16 00:48:30 momjian Exp $ +-- $Id: isbn_issn.sql.in,v 1.5 2002/07/30 17:34:37 tgl Exp $ -- -- -- Input and output functions and the type itself: @@ -232,190 +235,46 @@ create operator <> ( ); -------------------------------------------------- --- Create default operator class for 'isbn' -- --- Needed to create index or primary key -- -------------------------------------------------- - --- Register new operator class with system catalog pg_opclass -insert into pg_opclass - (opcamid, opcname, opcintype, opcdefault, opckeytype) - values ((select oid from pg_am where amname = 'btree'), - 'isbn_ops', - (select oid from pg_type where typname = 'isbn'), - true, - 0); - --- Verify that new operator class was added to pg_opclass --- select oid,* from pg_opclass where opcname = 'isbn_ops'; - --- Identify comparison operators for 'isbn' type -select o.oid as opoid, o.oprname - into temp table isbn_ops_tmp - from pg_operator o, pg_type t - where o.oprleft = t.oid - and o.oprright = t.oid - and t.typname = 'isbn'; - --- Make sure all 5 needed order ops are there (<, <=, =, >=, >) --- Operator <> will be present but is not needed --- select * from isbn_ops_tmp order by opoid; - --- Associate B-tree strategy 1 with < -insert into pg_amop - (amopclaid, amopstrategy, amopreqcheck, amopopr) - select opcl.oid, 1, false, c.opoid - from pg_opclass opcl, isbn_ops_tmp c - where opcamid = (select oid from pg_am where amname = 'btree') - and opcname = 'isbn_ops' - and c.oprname = '<'; - --- Associate B-tree strategy 2 with <= -insert into pg_amop - (amopclaid, amopstrategy, amopreqcheck, amopopr) - select opcl.oid, 2, false, c.opoid - from pg_opclass opcl, isbn_ops_tmp c - where opcamid = (select oid from pg_am where amname = 'btree') - and opcname = 'isbn_ops' - and c.oprname = '<='; - --- Associate B-tree strategy 3 with = -insert into pg_amop - (amopclaid, amopstrategy, amopreqcheck, amopopr) - select opcl.oid, 3, false, c.opoid - from pg_opclass opcl, isbn_ops_tmp c - where opcamid = (select oid from pg_am where amname = 'btree') - and opcname = 'isbn_ops' - and c.oprname = '='; - --- Associate B-tree strategy 4 with >= -insert into pg_amop - (amopclaid, amopstrategy, amopreqcheck, amopopr) - select opcl.oid, 4, false, c.opoid - from pg_opclass opcl, isbn_ops_tmp c - where opcamid = (select oid from pg_am where amname = 'btree') - and opcname = 'isbn_ops' - and c.oprname = '>='; - --- Associate B-tree strategy 5 with > -insert into pg_amop - (amopclaid, amopstrategy, amopreqcheck, amopopr) - select opcl.oid, 5, false, c.opoid - from pg_opclass opcl, isbn_ops_tmp c - where opcamid = (select oid from pg_am where amname = 'btree') - and opcname = 'isbn_ops' - and c.oprname = '>'; - -- Register 'isbn' comparison function create function isbn_cmp(isbn, isbn) returns integer as '$libdir/isbn_issn' language c; --- Make sure that function was correctly registered --- select oid, proname from pg_proc where proname = 'isbn_cmp'; +-- Register 'issn' comparison function +create function issn_cmp(issn, issn) + returns integer + as '$libdir/isbn_issn' + language c; --- Associate default btree operator class with 'isbn' comparison function -insert into pg_amproc - (amopclaid, amprocnum, amproc) - select opcl.oid, 1, p.oid - from pg_opclass opcl, pg_proc p - where opcamid = (select oid from pg_am where amname = 'btree') - and opcname = 'isbn_ops' - and p.proname = 'isbn_cmp'; +------------------------------------------------- +-- Create default operator class for 'isbn' -- +-- Needed to create index or primary key -- +------------------------------------------------- +CREATE OPERATOR CLASS isbn_ops + DEFAULT FOR TYPE isbn USING btree AS + OPERATOR 1 < , + OPERATOR 2 <= , + OPERATOR 3 = , + OPERATOR 4 >= , + OPERATOR 5 > , + FUNCTION 1 isbn_cmp(isbn, isbn); ------------------------------------------------- -- Create default operator class for 'issn' -- -- Needed to create index or primary key -- ------------------------------------------------- --- Register new operator class with system catalog pg_opclass -insert into pg_opclass - (opcamid, opcname, opcintype, opcdefault, opckeytype) - values ((select oid from pg_am where amname = 'btree'), - 'issn_ops', - (select oid from pg_type where typname = 'issn'), - true, - 0); - --- Verify that new operator class was added to pg_opclass --- select oid,* from pg_opclass where opcname = 'issn_ops'; - --- Identify comparison operators for 'issn' type -select o.oid as opoid, o.oprname - into temp table issn_ops_tmp - from pg_operator o, pg_type t - where o.oprleft = t.oid - and o.oprright = t.oid - and t.typname = 'issn'; - --- Make sure all 5 needed order ops are there (<, <=, =, >=, >) --- Operator <> will be present but is not needed --- select * from issn_ops_tmp order by opoid; - --- Associate B-tree strategy 1 with < -insert into pg_amop - (amopclaid, amopstrategy, amopreqcheck, amopopr) - select opcl.oid, 1, false, c.opoid - from pg_opclass opcl, issn_ops_tmp c - where opcamid = (select oid from pg_am where amname = 'btree') - and opcname = 'issn_ops' - and c.oprname = '<'; - --- Associate B-tree strategy 2 with <= -insert into pg_amop - (amopclaid, amopstrategy, amopreqcheck, amopopr) - select opcl.oid, 2, false, c.opoid - from pg_opclass opcl, issn_ops_tmp c - where opcamid = (select oid from pg_am where amname = 'btree') - and opcname = 'issn_ops' - and c.oprname = '<='; - --- Associate B-tree strategy 3 with = -insert into pg_amop - (amopclaid, amopstrategy, amopreqcheck, amopopr) - select opcl.oid, 3, false, c.opoid - from pg_opclass opcl, issn_ops_tmp c - where opcamid = (select oid from pg_am where amname = 'btree') - and opcname = 'issn_ops' - and c.oprname = '='; - --- Associate B-tree strategy 4 with >= -insert into pg_amop - (amopclaid, amopstrategy, amopreqcheck, amopopr) - select opcl.oid, 4, false, c.opoid - from pg_opclass opcl, issn_ops_tmp c - where opcamid = (select oid from pg_am where amname = 'btree') - and opcname = 'issn_ops' - and c.oprname = '>='; - --- Associate B-tree strategy 5 with > -insert into pg_amop - (amopclaid, amopstrategy, amopreqcheck, amopopr) - select opcl.oid, 5, false, c.opoid - from pg_opclass opcl, issn_ops_tmp c - where opcamid = (select oid from pg_am where amname = 'btree') - and opcname = 'issn_ops' - and c.oprname = '>'; - --- Register 'issn' comparison function -create function issn_cmp(issn, issn) - returns integer - as '$libdir/issn_issn' - language c; +CREATE OPERATOR CLASS issn_ops + DEFAULT FOR TYPE issn USING btree AS + OPERATOR 1 < , + OPERATOR 2 <= , + OPERATOR 3 = , + OPERATOR 4 >= , + OPERATOR 5 > , + FUNCTION 1 issn_cmp(issn, issn); --- Make sure that function was correctly registered --- select oid, proname from pg_proc where proname = 'issn_cmp'; - --- Associate default btree operator class with 'issn' comparison function -insert into pg_amproc - (amopclaid, amprocnum, amproc) - select opcl.oid, 1, p.oid - from pg_opclass opcl, pg_proc p - where opcamid = (select oid from pg_am where amname = 'btree') - and opcname = 'issn_ops' - and p.proname = 'issn_cmp'; -- -- eof diff --git a/contrib/rtree_gist/rtree_gist.sql.in b/contrib/rtree_gist/rtree_gist.sql.in index 0d1770381f4..df254404e18 100644 --- a/contrib/rtree_gist/rtree_gist.sql.in +++ b/contrib/rtree_gist/rtree_gist.sql.in @@ -1,4 +1,8 @@ begin transaction; + +-- Adjust this setting to control where the objects get created. +SET search_path = public; + -- -- -- @@ -21,157 +25,25 @@ create function gbox_union(bytea, opaque) returns box as 'MODULE_PATHNAME' langu create function gbox_same(box, box, opaque) returns opaque as 'MODULE_PATHNAME' language 'C'; --- add a new opclass -INSERT INTO pg_opclass (opcamid, opcname, opcnamespace, opcowner, opcintype, opcdefault, opckeytype) - VALUES ( - (SELECT oid FROM pg_am WHERE amname = 'gist'), - 'gist_box_ops', - (SELECT oid FROM pg_namespace WHERE nspname = 'pg_catalog'), - 1, -- UID of superuser is hardwired to 1 as of PG 7.3 - (SELECT oid FROM pg_type WHERE typname = 'box'), - true, - 0); - --- get the comparators for boxes and store them in a tmp table -SELECT o.oid AS opoid, o.oprname -INTO TEMP TABLE rt_ops_tmp -FROM pg_operator o, pg_type t -WHERE o.oprleft = t.oid - and t.typname = 'box'; - --- using the tmp table, generate the amop entries --- box_left -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 1, false, c.opoid - FROM pg_opclass opcl, rt_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_box_ops' - and c.oprname = '<<'; - --- box_overleft -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 2, false, c.opoid - FROM pg_opclass opcl, rt_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_box_ops' - and c.oprname = '&<'; - --- box_overlap -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 3, false, c.opoid - FROM pg_opclass opcl, rt_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_box_ops' - and c.oprname = '&&'; - --- box_overright -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 4, false, c.opoid - FROM pg_opclass opcl, rt_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_box_ops' - and c.oprname = '&>'; - --- box_right -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 5, false, c.opoid - FROM pg_opclass opcl, rt_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_box_ops' - and c.oprname = '>>'; - --- box_same -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 6, false, c.opoid - FROM pg_opclass opcl, rt_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_box_ops' - and c.oprname = '~='; - --- box_contains -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 7, false, c.opoid - FROM pg_opclass opcl, rt_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_box_ops' - and c.oprname = '~'; - --- box_contained -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 8, false, c.opoid - FROM pg_opclass opcl, rt_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_box_ops' - and c.oprname = '@'; - -DROP table rt_ops_tmp; - --- add the entries to amproc for the support methods --- note the amprocnum numbers associated with each are specific! - -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 1, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_box_ops' - and proname = 'gbox_consistent'; - -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 2, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_box_ops' - and proname = 'gbox_union'; - -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 3, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_box_ops' - and proname = 'gbox_compress'; - -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 4, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_box_ops' - and proname = 'rtree_decompress'; - -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 5, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_box_ops' - and proname = 'gbox_penalty'; - -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 6, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_box_ops' - and proname = 'gbox_picksplit'; +-- create the operator class +CREATE OPERATOR CLASS gist_box_ops + DEFAULT FOR TYPE box USING gist AS + OPERATOR 1 << , + OPERATOR 2 &< , + OPERATOR 3 && , + OPERATOR 4 &> , + OPERATOR 5 >> , + OPERATOR 6 ~= , + OPERATOR 7 ~ , + OPERATOR 8 @ , + FUNCTION 1 gbox_consistent (opaque, box, int4), + FUNCTION 2 gbox_union (bytea, opaque), + FUNCTION 3 gbox_compress (opaque), + FUNCTION 4 rtree_decompress (opaque), + FUNCTION 5 gbox_penalty (opaque, opaque, opaque), + FUNCTION 6 gbox_picksplit (opaque, opaque), + FUNCTION 7 gbox_same (box, box, opaque); -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 7, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_box_ops' - and proname = 'gbox_same'; -- -- @@ -185,158 +57,25 @@ create function gpoly_consistent(opaque,polygon,int4) returns bool as 'MODULE_PA create function gpoly_compress(opaque) returns opaque as 'MODULE_PATHNAME' language 'C'; --- add a new opclass -INSERT INTO pg_opclass (opcamid, opcname, opcnamespace, opcowner, opcintype, opcdefault, opckeytype) - VALUES ( - (SELECT oid FROM pg_am WHERE amname = 'gist'), - 'gist_poly_ops', - (SELECT oid FROM pg_namespace WHERE nspname = 'pg_catalog'), - 1, -- UID of superuser is hardwired to 1 as of PG 7.3 - (SELECT oid FROM pg_type WHERE typname = 'polygon'), - true, - (SELECT oid FROM pg_type WHERE typname = 'box')); +-- create the operator class +CREATE OPERATOR CLASS gist_poly_ops + DEFAULT FOR TYPE polygon USING gist AS + OPERATOR 1 << RECHECK , + OPERATOR 2 &< RECHECK , + OPERATOR 3 && RECHECK , + OPERATOR 4 &> RECHECK , + OPERATOR 5 >> RECHECK , + OPERATOR 6 ~= RECHECK , + OPERATOR 7 ~ RECHECK , + OPERATOR 8 @ RECHECK , + FUNCTION 1 gpoly_consistent (opaque, polygon, int4), + FUNCTION 2 gbox_union (bytea, opaque), + FUNCTION 3 gpoly_compress (opaque), + FUNCTION 4 rtree_decompress (opaque), + FUNCTION 5 gbox_penalty (opaque, opaque, opaque), + FUNCTION 6 gbox_picksplit (opaque, opaque), + FUNCTION 7 gbox_same (box, box, opaque), + STORAGE box; --- get the comparators for polygons and store them in a tmp table --- hack for 757 (poly_contain_pt) Teodor -SELECT o.oid AS opoid, o.oprname -INTO TEMP TABLE rt_ops_tmp -FROM pg_operator o, pg_type t -WHERE o.oprleft = t.oid and o.oid <> 757 - and t.typname = 'polygon'; - --- using the tmp table, generate the amop entries --- poly_left -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 1, true, c.opoid - FROM pg_opclass opcl, rt_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_poly_ops' - and c.oprname = '<<'; - --- poly_overleft -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 2, true, c.opoid - FROM pg_opclass opcl, rt_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_poly_ops' - and c.oprname = '&<'; - --- poly_overlap -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 3, true, c.opoid - FROM pg_opclass opcl, rt_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_poly_ops' - and c.oprname = '&&'; - --- poly_overright -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 4, true, c.opoid - FROM pg_opclass opcl, rt_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_poly_ops' - and c.oprname = '&>'; - --- poly_right -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 5, true, c.opoid - FROM pg_opclass opcl, rt_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_poly_ops' - and c.oprname = '>>'; - --- poly_same -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 6, true, c.opoid - FROM pg_opclass opcl, rt_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_poly_ops' - and c.oprname = '~='; - --- poly_contains -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 7, true, c.opoid - FROM pg_opclass opcl, rt_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_poly_ops' - and c.oprname = '~'; - --- poly_contained -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 8, true, c.opoid - FROM pg_opclass opcl, rt_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_poly_ops' - and c.oprname = '@'; - -DROP table rt_ops_tmp; - --- add the entries to amproc for the support methods --- note the amprocnum numbers associated with each are specific! - -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 1, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_poly_ops' - and proname = 'gpoly_consistent'; - -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 2, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_poly_ops' - and proname = 'gbox_union'; - -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 3, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_poly_ops' - and proname = 'gpoly_compress'; - -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 4, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_poly_ops' - and proname = 'rtree_decompress'; - -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 5, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_poly_ops' - and proname = 'gbox_penalty'; - -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 6, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_poly_ops' - and proname = 'gbox_picksplit'; - -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 7, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_poly_ops' - and proname = 'gbox_same'; end transaction; - diff --git a/contrib/seg/seg.sql.in b/contrib/seg/seg.sql.in index f8acddeb352..2da70ac682b 100644 --- a/contrib/seg/seg.sql.in +++ b/contrib/seg/seg.sql.in @@ -2,6 +2,9 @@ -- BEGIN TRANSACTION; +-- Adjust this setting to control where the objects get created. +SET search_path = public; + CREATE FUNCTION seg_in(opaque) RETURNS opaque AS 'MODULE_PATHNAME' @@ -235,162 +238,25 @@ CREATE FUNCTION gseg_same(seg, seg, opaque) RETURNS opaque AS 'MODULE_PATHNAME' LANGUAGE 'c'; --- register the default opclass for indexing -INSERT INTO pg_opclass (opcamid, opcname, opcnamespace, opcowner, opcintype, opcdefault, opckeytype) - VALUES ( - (SELECT oid FROM pg_am WHERE amname = 'gist'), - 'gist_seg_ops', - (SELECT oid FROM pg_namespace WHERE nspname = 'pg_catalog'), - 1, -- UID of superuser is hardwired to 1 as of PG 7.3 - (SELECT oid FROM pg_type WHERE typname = 'seg'), - true, - 0); - - --- get the comparators for segments and store them in a tmp table -SELECT o.oid AS opoid, o.oprname -INTO TEMP TABLE seg_ops_tmp -FROM pg_operator o, pg_type t -WHERE o.oprleft = t.oid and o.oprright = t.oid - and t.typname = 'seg'; - --- make sure we have the right operators --- SELECT * from seg_ops_tmp; - --- using the tmp table, generate the amop entries - --- seg_left -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 1, false, c.opoid - FROM pg_opclass opcl, seg_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_seg_ops' - and c.oprname = '<<'; - --- seg_overleft -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 2, false, c.opoid - FROM pg_opclass opcl, seg_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_seg_ops' - and c.oprname = '&<'; - --- seg_overlap -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 3, false, c.opoid - FROM pg_opclass opcl, seg_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_seg_ops' - and c.oprname = '&&'; - --- seg_overright -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 4, false, c.opoid - FROM pg_opclass opcl, seg_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_seg_ops' - and c.oprname = '&>'; - --- seg_right -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 5, false, c.opoid - FROM pg_opclass opcl, seg_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_seg_ops' - and c.oprname = '>>'; - --- seg_same -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 6, false, c.opoid - FROM pg_opclass opcl, seg_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_seg_ops' - and c.oprname = '='; - --- seg_contains -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 7, false, c.opoid - FROM pg_opclass opcl, seg_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_seg_ops' - and c.oprname = '@'; - --- seg_contained -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 8, false, c.opoid - FROM pg_opclass opcl, seg_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_seg_ops' - and c.oprname = '~'; - -DROP TABLE seg_ops_tmp; - - --- add the entries to amproc for the support methods --- note the amprocnum numbers associated with each are specific! - -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 1, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_seg_ops' - and proname = 'gseg_consistent'; - -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 2, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_seg_ops' - and proname = 'gseg_union'; - -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 3, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_seg_ops' - and proname = 'gseg_compress'; - -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 4, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_seg_ops' - and proname = 'gseg_decompress'; - -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 5, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_seg_ops' - and proname = 'gseg_penalty'; - -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 6, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_seg_ops' - and proname = 'gseg_picksplit'; - -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 7, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_seg_ops' - and proname = 'gseg_same'; +-- Create the operator class for indexing + +CREATE OPERATOR CLASS gist_seg_ops + DEFAULT FOR TYPE seg USING gist AS + OPERATOR 1 << , + OPERATOR 2 &< , + OPERATOR 3 && , + OPERATOR 4 &> , + OPERATOR 5 >> , + OPERATOR 6 = , + OPERATOR 7 @ , + OPERATOR 8 ~ , + FUNCTION 1 gseg_consistent (opaque, seg, int4), + FUNCTION 2 gseg_union (bytea, opaque), + FUNCTION 3 gseg_compress (opaque), + FUNCTION 4 gseg_decompress (opaque), + FUNCTION 5 gseg_penalty (opaque, opaque, opaque), + FUNCTION 6 gseg_picksplit (opaque, opaque), + FUNCTION 7 gseg_same (seg, seg, opaque); + END TRANSACTION; diff --git a/contrib/tsearch/tsearch.sql.in b/contrib/tsearch/tsearch.sql.in index e53793cf718..356427f2a46 100644 --- a/contrib/tsearch/tsearch.sql.in +++ b/contrib/tsearch/tsearch.sql.in @@ -1,5 +1,8 @@ BEGIN TRANSACTION; +-- Adjust this setting to control where the objects get created. +SET search_path = public; + -- TXTIDX type CREATE FUNCTION txtidx_in(opaque) @@ -134,6 +137,7 @@ input = gtxtidx_in, output = gtxtidx_out ); +-- support functions CREATE FUNCTION gtxtidx_consistent(gtxtidx,opaque,int4) RETURNS bool AS 'MODULE_PATHNAME' LANGUAGE 'c'; @@ -155,97 +159,19 @@ CREATE FUNCTION gtxtidx_union(bytea, opaque) RETURNS _int4 CREATE FUNCTION gtxtidx_same(gtxtidx, gtxtidx, opaque) RETURNS opaque AS 'MODULE_PATHNAME' LANGUAGE 'c'; -INSERT INTO pg_opclass (opcamid, opcname, opcnamespace, opcowner, opcintype, opcdefault, opckeytype) - VALUES ( - (SELECT oid FROM pg_am WHERE amname = 'gist'), - 'gist_txtidx_ops', - (SELECT oid FROM pg_namespace WHERE nspname = 'pg_catalog'), - 1, -- UID of superuser is hardwired to 1 as of PG 7.3 - (SELECT oid FROM pg_type WHERE typname = 'txtidx'), - true, - (SELECT oid FROM pg_type WHERE typname = 'gtxtidx')); - -SELECT o.oid AS opoid, o.oprname -INTO TEMP TABLE txtidx_ops_tmp -FROM pg_operator o, pg_type t, pg_type tq -WHERE o.oprleft = t.oid and o.oprright=tq.oid - and t.typname = 'txtidx' - and ( tq.typname='query_txt' or tq.typname='mquery_txt' ); - -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 1, true, c.opoid - FROM pg_opclass opcl, txtidx_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_txtidx_ops' - and c.oprname = '@@'; - -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 2, true, c.opoid - FROM pg_opclass opcl, txtidx_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_txtidx_ops' - and c.oprname = '##'; - -DROP TABLE txtidx_ops_tmp; - -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 1, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_txtidx_ops' - and proname = 'gtxtidx_consistent'; - -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 2, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_txtidx_ops' - and proname = 'gtxtidx_union'; - -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 3, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_txtidx_ops' - and proname = 'gtxtidx_compress'; - -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 4, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_txtidx_ops' - and proname = 'gtxtidx_decompress'; - -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 5, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_txtidx_ops' - and proname = 'gtxtidx_penalty'; - -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 6, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_txtidx_ops' - and proname = 'gtxtidx_picksplit'; - -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 7, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') - and opcname = 'gist_txtidx_ops' - and proname = 'gtxtidx_same'; - +-- create the operator class +CREATE OPERATOR CLASS gist_txtidx_ops + DEFAULT FOR TYPE txtidx USING gist AS + OPERATOR 1 @@ (txtidx, query_txt) RECHECK , + OPERATOR 2 ## (txtidx, mquery_txt) RECHECK , + FUNCTION 1 gtxtidx_consistent (gtxtidx, opaque, int4), + FUNCTION 2 gtxtidx_union (bytea, opaque), + FUNCTION 3 gtxtidx_compress (opaque), + FUNCTION 4 gtxtidx_decompress (opaque), + FUNCTION 5 gtxtidx_penalty (opaque, opaque, opaque), + FUNCTION 6 gtxtidx_picksplit (opaque, opaque), + FUNCTION 7 gtxtidx_same (gtxtidx, gtxtidx, opaque), + STORAGE gtxtidx; END TRANSACTION; |