1 -- PostRR - src/postrr.sql
2 -- Copyright (C) 2012 Sebastian 'tokkee' Harl <sh@tokkee.org>
3 -- All rights reserved.
4 --
5 -- Redistribution and use in source and binary forms, with or without
6 -- modification, are permitted provided that the following conditions
7 -- are met:
8 -- 1. Redistributions of source code must retain the above copyright
9 -- notice, this list of conditions and the following disclaimer.
10 -- 2. Redistributions in binary form must reproduce the above copyright
11 -- notice, this list of conditions and the following disclaimer in the
12 -- documentation and/or other materials provided with the distribution.
13 --
14 -- THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
15 -- ``AS IS'' AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED
16 -- TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
17 -- PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDERS OR
18 -- CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
19 -- EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
20 -- PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS;
21 -- OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY,
22 -- WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR
23 -- OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF
24 -- ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 --
27 -- PostRR - PostgreSQL Round-Robin Extension
28 --
30 -- suppress messages like 'return type foo is only a shell'
31 SET client_min_messages TO WARNING;
33 BEGIN;
35 CREATE SCHEMA postrr;
37 CREATE SEQUENCE postrr.tsid INCREMENT BY 1
38 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 NO CYCLE;
40 CREATE TABLE postrr.rrtimeslices (
41 tsid integer NOT NULL PRIMARY KEY
42 DEFAULT nextval('postrr.tsid'::regclass)
43 CHECK (0 < tsid),
44 tslen integer NOT NULL,
45 tsnum integer NOT NULL
46 );
48 CREATE TABLE postrr.rrarchives (
49 rraname text NOT NULL,
50 tbl name NOT NULL,
51 tscol name NOT NULL,
52 vcol name NOT NULL,
53 UNIQUE (rraname, tbl, tscol, vcol)
54 );
56 CREATE OR REPLACE FUNCTION PostRR_Version()
57 RETURNS cstring
58 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'postrr_version'
59 LANGUAGE 'C' IMMUTABLE;
61 CREATE TYPE RRTimeslice;
63 CREATE OR REPLACE FUNCTION RRTimeslice_validate(integer)
64 RETURNS cstring
65 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_validate'
66 LANGUAGE 'C' IMMUTABLE STRICT;
68 -- this will abort the transaction in case the expected internal length does
69 -- not match the actual length
70 SELECT RRTimeslice_validate(16);
72 CREATE OR REPLACE FUNCTION RRTimeslice_in(cstring, oid, integer)
73 RETURNS RRTimeslice
74 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_in'
75 LANGUAGE 'C' IMMUTABLE STRICT;
77 CREATE OR REPLACE FUNCTION RRTimeslice_out(RRTimeslice)
78 RETURNS cstring
79 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_out'
80 LANGUAGE 'C' IMMUTABLE STRICT;
82 CREATE OR REPLACE FUNCTION RRTimeslice_typmodin(cstring[])
83 RETURNS integer
84 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_typmodin'
85 LANGUAGE 'C' IMMUTABLE STRICT;
87 CREATE OR REPLACE FUNCTION RRTimeslice_typmodout(integer)
88 RETURNS cstring
89 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_typmodout'
90 LANGUAGE 'C' IMMUTABLE STRICT;
92 CREATE TYPE RRTimeslice (
93 INTERNALLENGTH = 16,
94 INPUT = RRTimeslice_in,
95 OUTPUT = RRTimeslice_out,
96 TYPMOD_IN = RRTimeslice_typmodin,
97 TYPMOD_OUT = RRTimeslice_typmodout,
98 ALIGNMENT = double,
99 STORAGE = plain
100 );
102 CREATE OR REPLACE FUNCTION RRTimeslice(rrtimeslice, integer, boolean)
103 RETURNS rrtimeslice
104 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_to_rrtimeslice'
105 LANGUAGE 'C' IMMUTABLE STRICT;
107 CREATE CAST (rrtimeslice AS rrtimeslice)
108 WITH FUNCTION RRTimeslice(rrtimeslice, integer, boolean)
109 AS IMPLICIT;
111 CREATE OR REPLACE FUNCTION Tstamp(rrtimeslice)
112 RETURNS timestamp
113 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_to_timestamp'
114 LANGUAGE 'C' IMMUTABLE STRICT;
116 CREATE CAST (rrtimeslice AS timestamp)
117 WITH FUNCTION Tstamp(rrtimeslice);
118 -- EXPLICIT
120 CREATE OR REPLACE FUNCTION rrtimeslice_seq_eq(rrtimeslice, rrtimeslice)
121 RETURNS boolean
122 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_seq_eq'
123 LANGUAGE 'C' IMMUTABLE STRICT;
125 CREATE OR REPLACE FUNCTION rrtimeslice_seq_ne(rrtimeslice, rrtimeslice)
126 RETURNS boolean
127 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_seq_ne'
128 LANGUAGE 'C' IMMUTABLE STRICT;
130 CREATE OR REPLACE FUNCTION rrtimeslice_seq_lt(rrtimeslice, rrtimeslice)
131 RETURNS boolean
132 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_seq_lt'
133 LANGUAGE 'C' IMMUTABLE STRICT;
135 CREATE OR REPLACE FUNCTION rrtimeslice_seq_le(rrtimeslice, rrtimeslice)
136 RETURNS boolean
137 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_seq_le'
138 LANGUAGE 'C' IMMUTABLE STRICT;
140 CREATE OR REPLACE FUNCTION rrtimeslice_seq_gt(rrtimeslice, rrtimeslice)
141 RETURNS boolean
142 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_seq_gt'
143 LANGUAGE 'C' IMMUTABLE STRICT;
145 CREATE OR REPLACE FUNCTION rrtimeslice_seq_ge(rrtimeslice, rrtimeslice)
146 RETURNS boolean
147 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_seq_ge'
148 LANGUAGE 'C' IMMUTABLE STRICT;
150 CREATE OR REPLACE FUNCTION rrtimeslice_seq_cmp(rrtimeslice, rrtimeslice)
151 RETURNS integer
152 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_seq_cmp'
153 LANGUAGE 'C' IMMUTABLE STRICT;
155 CREATE OR REPLACE FUNCTION rrtimeslice_seq_hash(rrtimeslice)
156 RETURNS integer
157 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_seq_hash'
158 LANGUAGE 'C' IMMUTABLE STRICT;
160 CREATE OPERATOR = (
161 LEFTARG = RRTimeslice,
162 RIGHTARG = RRTimeslice,
163 PROCEDURE = rrtimeslice_seq_eq,
164 COMMUTATOR = =,
165 NEGATOR = <>,
166 RESTRICT = eqsel
167 );
169 CREATE OPERATOR <> (
170 LEFTARG = RRTimeslice,
171 RIGHTARG = RRTimeslice,
172 PROCEDURE = rrtimeslice_seq_ne,
173 COMMUTATOR = <>,
174 NEGATOR = =,
175 RESTRICT = neqsel
176 );
178 CREATE OPERATOR < (
179 LEFTARG = RRTimeslice,
180 RIGHTARG = RRTimeslice,
181 PROCEDURE = rrtimeslice_seq_lt,
182 COMMUTATOR = >,
183 NEGATOR = <=,
184 RESTRICT = scalarltsel
185 );
187 CREATE OPERATOR <= (
188 LEFTARG = RRTimeslice,
189 RIGHTARG = RRTimeslice,
190 PROCEDURE = rrtimeslice_seq_le,
191 COMMUTATOR = >=,
192 NEGATOR = <,
193 RESTRICT = scalarltsel
194 );
196 CREATE OPERATOR > (
197 LEFTARG = RRTimeslice,
198 RIGHTARG = RRTimeslice,
199 PROCEDURE = rrtimeslice_seq_gt,
200 COMMUTATOR = <,
201 NEGATOR = >=,
202 RESTRICT = scalargtsel
203 );
205 CREATE OPERATOR >= (
206 LEFTARG = RRTimeslice,
207 RIGHTARG = RRTimeslice,
208 PROCEDURE = rrtimeslice_seq_ge,
209 COMMUTATOR = <=,
210 NEGATOR = >,
211 RESTRICT = scalargtsel
212 );
214 CREATE OPERATOR CLASS rrtimeslice_ops
215 DEFAULT FOR TYPE RRTimeslice USING btree AS
216 OPERATOR 1 < ,
217 OPERATOR 2 <= ,
218 OPERATOR 3 = ,
219 OPERATOR 4 >= ,
220 OPERATOR 5 > ,
221 FUNCTION 1 rrtimeslice_seq_cmp(rrtimeslice, rrtimeslice);
223 CREATE OPERATOR CLASS rrtimeslice_hash_ops
224 FOR TYPE RRTimeslice USING hash AS
225 OPERATOR 1 = ,
226 FUNCTION 1 rrtimeslice_seq_hash(rrtimeslice);
228 CREATE TYPE CData;
230 CREATE OR REPLACE FUNCTION CData_validate(integer)
231 RETURNS cstring
232 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'cdata_validate'
233 LANGUAGE 'C' IMMUTABLE STRICT;
235 -- this will abort the transaction in case the expected internal length does
236 -- not match the actual length
237 SELECT CData_validate(24);
239 CREATE OR REPLACE FUNCTION CData_in(cstring, oid, integer)
240 RETURNS CData
241 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'cdata_in'
242 LANGUAGE 'C' IMMUTABLE STRICT;
244 CREATE OR REPLACE FUNCTION CData_out(CData)
245 RETURNS cstring
246 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'cdata_out'
247 LANGUAGE 'C' IMMUTABLE STRICT;
249 CREATE OR REPLACE FUNCTION CData_typmodin(cstring[])
250 RETURNS integer
251 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'cdata_typmodin'
252 LANGUAGE 'C' IMMUTABLE STRICT;
254 CREATE OR REPLACE FUNCTION CData_typmodout(integer)
255 RETURNS cstring
256 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'cdata_typmodout'
257 LANGUAGE 'C' IMMUTABLE STRICT;
259 CREATE TYPE CData (
260 INTERNALLENGTH = 24,
261 INPUT = CData_in,
262 OUTPUT = CData_out,
263 TYPMOD_IN = CData_typmodin,
264 TYPMOD_OUT = CData_typmodout,
265 ALIGNMENT = double,
266 STORAGE = plain
267 );
269 CREATE OR REPLACE FUNCTION CData(cdata, integer, boolean)
270 RETURNS cdata
271 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'cdata_to_cdata'
272 LANGUAGE 'C' IMMUTABLE STRICT;
274 CREATE CAST (cdata AS cdata)
275 WITH FUNCTION CData(cdata, integer, boolean)
276 AS IMPLICIT;
278 CREATE CAST (numeric AS cdata)
279 WITH INOUT
280 AS ASSIGNMENT;
282 CREATE OR REPLACE FUNCTION CData(integer, integer, boolean)
283 RETURNS cdata
284 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'int32_to_cdata'
285 LANGUAGE 'C' IMMUTABLE STRICT;
287 CREATE CAST (integer AS cdata)
288 WITH FUNCTION CData(integer, integer, boolean)
289 AS ASSIGNMENT;
291 CREATE OR REPLACE FUNCTION Float8(cdata)
292 RETURNS double precision
293 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'cdata_to_float8'
294 LANGUAGE 'C' IMMUTABLE STRICT;
296 CREATE CAST (cdata AS double precision)
297 WITH FUNCTION Float8(cdata);
298 -- EXPLICIT
300 CREATE OR REPLACE FUNCTION CData_update(cdata, cdata)
301 RETURNS cdata
302 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'cdata_update'
303 LANGUAGE 'C' IMMUTABLE;
305 COMMIT;
307 SET client_min_messages TO DEFAULT;
309 -- vim: set tw=78 sw=4 ts=4 noexpandtab :