From 17c5231ae64bd5b61abc13db6081f35d5da3ec44 Mon Sep 17 00:00:00 2001 From: Nathan Wagner Date: Tue, 23 Sep 2014 04:07:51 +0000 Subject: [PATCH] Add support for gin indexes on uuid arrays. Added a default operator class for gin indexes on uuid[]. Since we want to support arrays containing nulls, the operator class uses existing array support methods. --- postgres/Makefile | 5 +- postgres/pduuid--1.0.1--1.1.sql | 14 +++ postgres/pduuid--1.1.sql | 151 ++++++++++++++++++++++++++++++++ postgres/pduuid.control | 2 +- 4 files changed, 170 insertions(+), 2 deletions(-) create mode 100644 postgres/pduuid--1.0.1--1.1.sql create mode 100644 postgres/pduuid--1.1.sql diff --git a/postgres/Makefile b/postgres/Makefile index f18fad1..c6cf973 100644 --- a/postgres/Makefile +++ b/postgres/Makefile @@ -1,9 +1,12 @@ # makefile for postgres uuid type support functions # written by nathan wagner and placed in the public domain +VERSIONS=1.0 1.0.1 1.1 +UPGRADES=1.0--1.0.1 1.0.1--1.1 + EXTENSION=pduuid MODULES= pduuid -DATA= pduuid--1.0.1.sql pduuid--1.0.sql pduuid--1.0--1.0.1.sql +DATA= $(addprefix $(EXTENSION)--, $(addsuffix .sql, $(VERSIONS) $(UPGRADES))) #SHLIB_LINK= -Lpduuid -lpduuid MODULE_big= pduuid OBJS= uuid.o ../internal.o ../rng.o ../md5.o ../sha1.o diff --git a/postgres/pduuid--1.0.1--1.1.sql b/postgres/pduuid--1.0.1--1.1.sql new file mode 100644 index 0000000..ea18018 --- /dev/null +++ b/postgres/pduuid--1.0.1--1.1.sql @@ -0,0 +1,14 @@ +CREATE OPERATOR CLASS gin__uuid_ops +DEFAULT +FOR TYPE _uuid USING gin +AS + OPERATOR 1 pg_catalog.&& (anyarray, anyarray), + OPERATOR 2 pg_catalog.@> (anyarray, anyarray), + OPERATOR 3 pg_catalog.<@ (anyarray, anyarray), + OPERATOR 4 pg_catalog.= (anyarray, anyarray), + FUNCTION 1 uuid_cmp (uuid, uuid), + FUNCTION 2 ginarrayextract (anyarray, internal, internal), + FUNCTION 3 ginqueryarrayextract (anyarray, internal, int2, internal, internal, internal, internal), + FUNCTION 4 ginarrayconsistent (internal, int2, anyarray, int4, internal, internal, internal, internal), + STORAGE uuid +; diff --git a/postgres/pduuid--1.1.sql b/postgres/pduuid--1.1.sql new file mode 100644 index 0000000..8a70241 --- /dev/null +++ b/postgres/pduuid--1.1.sql @@ -0,0 +1,151 @@ +-- uuid support function install script +-- written by nathan wagner and placed in the public domain + +create function uuid_v1() returns uuid as 'pduuid', 'uuid_gen_v1' +language C strict; + +create function uuid_v1mc() returns uuid as 'pduuid', 'uuid_gen_v1_mc' +language C strict; + +create function uuid_v3(uuid, text) returns uuid +as 'pduuid', 'uuid_gen_v3' +language C strict; + +create function uuid_v4() returns uuid as 'pduuid', 'uuid_gen_v4' +language C strict; + +create function uuid_v5(uuid, text) returns uuid +as 'pduuid', 'uuid_gen_v5' +language C strict; + +create function uuid_recent() returns uuid as 'pduuid', 'uuid_recent' +language C strict; + +create function uuid_nil() returns uuid as 'pduuid', 'uuid_nil' +language C immutable strict; + +create function uuid_ns_dns() returns uuid as 'pduuid', 'uuid_dns' +language C immutable strict; + +create function uuid_ns_oid() returns uuid as 'pduuid', 'uuid_oid' +language C immutable strict; + +create function uuid_ns_url() returns uuid as 'pduuid', 'uuid_url' +language C immutable strict; + +create function uuid_ns_x500() returns uuid as 'pduuid', 'uuid_x500' +language C immutable strict; + +create function uuid_url(text) returns uuid as $$ +select uuid_v5(uuid_ns_url(), $1); +$$ +language 'sql' immutable strict; + +create function uuid_dns(text) returns uuid as $$ +select uuid_v5(uuid_ns_dns(), $1); +$$ +language 'sql' immutable strict; + +create function uuid_oid(text) returns uuid as $$ +select uuid_v5(uuid_ns_oid(), $1); +$$ +language 'sql' immutable strict; + +create function uuid_x500(text) returns uuid as $$ +select uuid_v5(uuid_ns_x500(), $1); +$$ +language 'sql' immutable strict; + +-- Casts + +-- Bytea + +create function uuid_bytea_cast(uuid) returns bytea as +'pduuid', 'uuid_cast_bytea' +language C strict; + +create function uuid_bytea_uncast(bytea) returns uuid as +'pduuid', 'uuid_cast_from_bytea' +language C strict; + +create cast (uuid as bytea) with function uuid_bytea_cast(uuid); + +create cast (bytea as uuid) with function uuid_bytea_uncast(bytea); + +-- Numeric + +create function uuid_numeric_cast(uuid) returns numeric as +'pduuid', 'uuid_cast_numeric' +language C strict; + +create function uuid_numeric_uncast(numeric) returns uuid as +'pduuid', 'uuid_cast_from_numeric' +language C strict; + +create cast (uuid as numeric) with function uuid_numeric_cast(uuid); + +create cast (numeric as uuid) with function uuid_numeric_uncast(numeric); + +-- Bit and bit varying + +create function uuid_bit_cast(uuid) returns bit(128) as +'pduuid', 'uuid_cast_bit' +language C strict; + +create function uuid_bit_uncast(bit(128)) returns uuid as +'pduuid', 'uuid_cast_from_bit' +language C strict; + +create cast (uuid as bit(128)) with function uuid_bit_cast(uuid); + +create cast (uuid as bit varying) with function uuid_bit_cast(uuid); + +create cast (bit(128) as uuid) with function uuid_bit_uncast(bit(128)); + +create cast (bit varying as uuid) with function uuid_bit_uncast(bit(128)); + +-- Field extraction functions + +create function uuid_version(uuid) returns integer as +'pduuid', 'uuid_extract_version' +language C strict; + +create function uuid_macaddr(uuid) returns macaddr as +'pduuid', 'uuid_extract_macaddr' +language C strict; + +create function uuid_timestamp(uuid) returns timestamp as +'pduuid', 'uuid_extract_timestamp' +language C strict; + +create function uuid_timestamptz(uuid) +returns timestamp with time zone as +'pduuid', 'uuid_extract_timestamp' +language C strict; + +-- TODO +-- cast to timestamp / extract timestamp +-- cast to macaddr / extract macaddr +-- extract variant +-- extract clock sequence + +-- Operation Strategy Number +-- overlap 1 +-- contains 2 +-- contained 3 +-- equal 4 + +CREATE OPERATOR CLASS gin__uuid_ops +DEFAULT +FOR TYPE _uuid USING gin +AS + OPERATOR 1 pg_catalog.&& (anyarray, anyarray), + OPERATOR 2 pg_catalog.@> (anyarray, anyarray), + OPERATOR 3 pg_catalog.<@ (anyarray, anyarray), + OPERATOR 4 pg_catalog.= (anyarray, anyarray), + FUNCTION 1 uuid_cmp (uuid, uuid), + FUNCTION 2 ginarrayextract (anyarray, internal, internal), + FUNCTION 3 ginqueryarrayextract (anyarray, internal, int2, internal, internal, internal, internal), + FUNCTION 4 ginarrayconsistent (internal, int2, anyarray, int4, internal, internal, internal, internal), + STORAGE uuid +; diff --git a/postgres/pduuid.control b/postgres/pduuid.control index 65c37fc..23f9055 100644 --- a/postgres/pduuid.control +++ b/postgres/pduuid.control @@ -1,3 +1,3 @@ relocatable = true comment = 'Public Domain uuid support functions' -default_version = '1.0.1' +default_version = '1.1' -- 2.40.0