1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
|
--
-- 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;
|
