ossp-pkg/uuid/pgsql/uuid.sql.in
1.10
--
-- OSSP uuid - Universally Unique Identifier
-- Copyright (c) 2004-2007 Ralf S. Engelschall <rse@engelschall.com>
-- Copyright (c) 2004-2007 The OSSP Project <http://www.ossp.org/>
--
-- This file is part of OSSP uuid, a library for the generation
-- of UUIDs which can found at http://www.ossp.org/pkg/lib/uuid/
--
-- Permission to use, copy, modify, and distribute this software for
-- any purpose with or without fee is hereby granted, provided that
-- the above copyright notice and this permission notice appear in all
-- copies.
--
-- THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
-- WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
-- MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
-- IN NO EVENT SHALL THE AUTHORS AND COPYRIGHT HOLDERS AND THEIR
-- CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
-- SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
-- LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
-- USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
-- ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
-- OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
-- OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
-- SUCH DAMAGE.
--
-- uuid.sql: PostgreSQL Binding (SQL part)
--
--
-- prolog
--
SET search_path TO public;
SET client_min_messages TO warning;
DROP FUNCTION uuid_in(CSTRING) CASCADE;
DROP FUNCTION uuid_out(uuid) CASCADE;
DROP FUNCTION uuid_recv(INTERNAL) CASCADE;
DROP FUNCTION uuid_send(uuid) CASCADE;
DROP TYPE uuid CASCADE;
DROP CAST (CSTRING AS uuid) CASCADE;
DROP CAST (uuid AS CSTRING) CASCADE;
DROP FUNCTION uuid(CSTRING) CASCADE;
DROP FUNCTION uuid(INTEGER) CASCADE;
DROP FUNCTION uuid(INTEGER, CSTRING, CSTRING) CASCADE;
DROP FUNCTION uuid_eq(uuid) CASCADE;
DROP FUNCTION uuid_ne(uuid) CASCADE;
DROP FUNCTION uuid_lt(uuid, uuid) CASCADE;
DROP FUNCTION uuid_gt(uuid, uuid) CASCADE;
DROP FUNCTION uuid_le(uuid, uuid) CASCADE;
DROP FUNCTION uuid_ge(uuid, uuid) CASCADE;
DROP OPERATOR =(uuid,uuid) CASCADE;
DROP OPERATOR <>(uuid,uuid) CASCADE;
DROP OPERATOR <(uuid,uuid) CASCADE;
DROP OPERATOR >(uuid,uuid) CASCADE;
DROP OPERATOR <=(uuid,uuid) CASCADE;
DROP OPERATOR >=(uuid,uuid) CASCADE;
DROP FUNCTION uuid_hash(uuid) CASCADE;
DROP FUNCTION uuid_cmp(uuid, uuid) CASCADE;
DROP OPERATOR CLASS uuid_ops USING hash CASCADE;
DROP OPERATOR CLASS uuid_ops USING btree CASCADE;
BEGIN;
--
-- the UUID data type
--
CREATE FUNCTION
uuid_in(CSTRING) RETURNS uuid
STRICT
LANGUAGE C AS 'MODULE_PATHNAME', 'pg_uuid_in';
CREATE FUNCTION
uuid_out(uuid) RETURNS CSTRING
STRICT
LANGUAGE C AS 'MODULE_PATHNAME', 'pg_uuid_out';
CREATE FUNCTION
uuid_recv(INTERNAL) RETURNS uuid
STRICT
LANGUAGE C AS 'MODULE_PATHNAME', 'pg_uuid_recv';
CREATE FUNCTION
uuid_send(uuid) RETURNS BYTEA
STRICT
LANGUAGE C AS 'MODULE_PATHNAME', 'pg_uuid_send';
CREATE TYPE uuid (
INPUT = uuid_in, -- for SQL input
OUTPUT = uuid_out, -- for SQL output
RECEIVE = uuid_recv, -- for DB input
SEND = uuid_send, -- for DB output
DEFAULT = 'uuid(1)',
INTERNALLENGTH = 16,
ALIGNMENT = char
);
COMMENT ON TYPE uuid
IS 'UUID type';
-- CREATE CAST (CSTRING AS uuid)
-- WITH FUNCTION uuid_in(CSTRING) AS ASSIGNMENT;
--
-- CREATE CAST (uuid AS CSTRING)
-- WITH FUNCTION uuid_out(uuid) AS ASSIGNMENT;
--
-- the UUID constructor function
--
CREATE FUNCTION
uuid(CSTRING) RETURNS uuid
IMMUTABLE STRICT
LANGUAGE C AS 'MODULE_PATHNAME', 'pg_uuid_in';
CREATE FUNCTION
uuid(INTEGER) RETURNS uuid
VOLATILE CALLED ON NULL INPUT
LANGUAGE C AS 'MODULE_PATHNAME', 'pg_uuid_make';
CREATE FUNCTION
uuid(INTEGER, CSTRING, CSTRING) RETURNS uuid
VOLATILE CALLED ON NULL INPUT
LANGUAGE C AS 'MODULE_PATHNAME', 'pg_uuid_make';
--
-- the UUID operators
--
CREATE FUNCTION
uuid_eq(uuid, uuid) RETURNS BOOL
IMMUTABLE STRICT
LANGUAGE C AS 'MODULE_PATHNAME', 'pg_uuid_eq';
CREATE FUNCTION
uuid_ne(uuid, uuid) RETURNS BOOL
IMMUTABLE STRICT
LANGUAGE C AS 'MODULE_PATHNAME', 'pg_uuid_ne';
CREATE FUNCTION
uuid_lt(uuid, uuid) RETURNS BOOL
IMMUTABLE STRICT
LANGUAGE C AS 'MODULE_PATHNAME', 'pg_uuid_lt';
CREATE FUNCTION
uuid_gt(uuid, uuid) RETURNS BOOL
IMMUTABLE STRICT
LANGUAGE C AS 'MODULE_PATHNAME', 'pg_uuid_gt';
CREATE FUNCTION
uuid_le(uuid, uuid) RETURNS BOOL
IMMUTABLE STRICT
LANGUAGE C AS 'MODULE_PATHNAME', 'pg_uuid_le';
CREATE FUNCTION
uuid_ge(uuid, uuid) RETURNS BOOL
IMMUTABLE STRICT
LANGUAGE C AS 'MODULE_PATHNAME', 'pg_uuid_ge';
CREATE OPERATOR = (
leftarg = uuid,
rightarg = uuid,
negator = <>,
procedure = uuid_eq
);
CREATE OPERATOR <> (
leftarg = uuid,
rightarg = uuid,
negator = =,
procedure = uuid_ne
);
CREATE OPERATOR < (
leftarg = uuid,
rightarg = uuid,
commutator = >,
negator = >=,
procedure = uuid_lt
);
CREATE OPERATOR > (
leftarg = uuid,
rightarg = uuid,
commutator = <,
negator = <=,
procedure = uuid_gt
);
CREATE OPERATOR <= (
leftarg = uuid,
rightarg = uuid,
commutator = >=,
negator = >,
procedure = uuid_le
);
CREATE OPERATOR >= (
leftarg = uuid,
rightarg = uuid,
commutator = <=,
negator = <,
procedure = uuid_ge
);
--
-- the UUID support for indexing
--
CREATE FUNCTION
uuid_hash(uuid) RETURNS INTEGER
IMMUTABLE STRICT
LANGUAGE C AS 'MODULE_PATHNAME', 'pg_uuid_hash';
CREATE FUNCTION
uuid_cmp(uuid, uuid) RETURNS INTEGER
IMMUTABLE STRICT
LANGUAGE C AS 'MODULE_PATHNAME', 'pg_uuid_cmp';
CREATE OPERATOR CLASS uuid_ops
DEFAULT FOR TYPE uuid USING hash AS
OPERATOR 1 =, -- 1: equal
FUNCTION 1 uuid_hash(uuid);
CREATE OPERATOR CLASS uuid_ops
DEFAULT FOR TYPE uuid USING btree AS
OPERATOR 1 <, -- 1: less than
OPERATOR 2 <=, -- 2: less than or equal
OPERATOR 3 =, -- 3: equal
OPERATOR 4 >=, -- 4: greater than or equal
OPERATOR 5 >, -- 5: greater than
FUNCTION 1 uuid_cmp(uuid, uuid);
--
-- epilog
--
COMMIT;
