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 Tstamptz(rrtimeslice)
112 RETURNS timestamptz
113 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_to_timestamptz'
114 LANGUAGE 'C' IMMUTABLE STRICT;
116 CREATE CAST (rrtimeslice AS timestamptz)
117 WITH FUNCTION Tstamptz(rrtimeslice);
118 -- EXPLICIT
120 CREATE OR REPLACE FUNCTION rrtimeslice_cmp(rrtimeslice, rrtimeslice)
121 RETURNS integer
122 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_cmp'
123 LANGUAGE 'C' IMMUTABLE STRICT;
125 CREATE OR REPLACE FUNCTION rrtimeslice_seq_eq(rrtimeslice, rrtimeslice)
126 RETURNS boolean
127 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_seq_eq'
128 LANGUAGE 'C' IMMUTABLE STRICT;
130 CREATE OR REPLACE FUNCTION rrtimeslice_seq_ne(rrtimeslice, rrtimeslice)
131 RETURNS boolean
132 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_seq_ne'
133 LANGUAGE 'C' IMMUTABLE STRICT;
135 CREATE OR REPLACE FUNCTION rrtimeslice_seq_lt(rrtimeslice, rrtimeslice)
136 RETURNS boolean
137 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_seq_lt'
138 LANGUAGE 'C' IMMUTABLE STRICT;
140 CREATE OR REPLACE FUNCTION rrtimeslice_seq_le(rrtimeslice, rrtimeslice)
141 RETURNS boolean
142 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_seq_le'
143 LANGUAGE 'C' IMMUTABLE STRICT;
145 CREATE OR REPLACE FUNCTION rrtimeslice_seq_gt(rrtimeslice, rrtimeslice)
146 RETURNS boolean
147 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_seq_gt'
148 LANGUAGE 'C' IMMUTABLE STRICT;
150 CREATE OR REPLACE FUNCTION rrtimeslice_seq_ge(rrtimeslice, rrtimeslice)
151 RETURNS boolean
152 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_seq_ge'
153 LANGUAGE 'C' IMMUTABLE STRICT;
155 CREATE OR REPLACE FUNCTION rrtimeslice_seq_cmp(rrtimeslice, rrtimeslice)
156 RETURNS integer
157 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_seq_cmp'
158 LANGUAGE 'C' IMMUTABLE STRICT;
160 CREATE OR REPLACE FUNCTION rrtimeslice_seq_hash(rrtimeslice)
161 RETURNS integer
162 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_seq_hash'
163 LANGUAGE 'C' IMMUTABLE STRICT;
165 CREATE OPERATOR = (
166 LEFTARG = RRTimeslice,
167 RIGHTARG = RRTimeslice,
168 PROCEDURE = rrtimeslice_seq_eq,
169 COMMUTATOR = =,
170 NEGATOR = <>,
171 RESTRICT = eqsel
172 );
174 CREATE OPERATOR <> (
175 LEFTARG = RRTimeslice,
176 RIGHTARG = RRTimeslice,
177 PROCEDURE = rrtimeslice_seq_ne,
178 COMMUTATOR = <>,
179 NEGATOR = =,
180 RESTRICT = neqsel
181 );
183 CREATE OPERATOR < (
184 LEFTARG = RRTimeslice,
185 RIGHTARG = RRTimeslice,
186 PROCEDURE = rrtimeslice_seq_lt,
187 COMMUTATOR = >,
188 NEGATOR = <=,
189 RESTRICT = scalarltsel
190 );
192 CREATE OPERATOR <= (
193 LEFTARG = RRTimeslice,
194 RIGHTARG = RRTimeslice,
195 PROCEDURE = rrtimeslice_seq_le,
196 COMMUTATOR = >=,
197 NEGATOR = <,
198 RESTRICT = scalarltsel
199 );
201 CREATE OPERATOR > (
202 LEFTARG = RRTimeslice,
203 RIGHTARG = RRTimeslice,
204 PROCEDURE = rrtimeslice_seq_gt,
205 COMMUTATOR = <,
206 NEGATOR = >=,
207 RESTRICT = scalargtsel
208 );
210 CREATE OPERATOR >= (
211 LEFTARG = RRTimeslice,
212 RIGHTARG = RRTimeslice,
213 PROCEDURE = rrtimeslice_seq_ge,
214 COMMUTATOR = <=,
215 NEGATOR = >,
216 RESTRICT = scalargtsel
217 );
219 CREATE OPERATOR CLASS rrtimeslice_ops
220 DEFAULT FOR TYPE RRTimeslice USING btree AS
221 OPERATOR 1 < ,
222 OPERATOR 2 <= ,
223 OPERATOR 3 = ,
224 OPERATOR 4 >= ,
225 OPERATOR 5 > ,
226 FUNCTION 1 rrtimeslice_seq_cmp(rrtimeslice, rrtimeslice);
228 CREATE OPERATOR CLASS rrtimeslice_hash_ops
229 FOR TYPE RRTimeslice USING hash AS
230 OPERATOR 1 = ,
231 FUNCTION 1 rrtimeslice_seq_hash(rrtimeslice);
233 CREATE TYPE CData;
235 CREATE OR REPLACE FUNCTION CData_validate(integer)
236 RETURNS cstring
237 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'cdata_validate'
238 LANGUAGE 'C' IMMUTABLE STRICT;
240 -- this will abort the transaction in case the expected internal length does
241 -- not match the actual length
242 SELECT CData_validate(24);
244 CREATE OR REPLACE FUNCTION CData_in(cstring, oid, integer)
245 RETURNS CData
246 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'cdata_in'
247 LANGUAGE 'C' IMMUTABLE STRICT;
249 CREATE OR REPLACE FUNCTION CData_out(CData)
250 RETURNS cstring
251 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'cdata_out'
252 LANGUAGE 'C' IMMUTABLE STRICT;
254 CREATE OR REPLACE FUNCTION CData_typmodin(cstring[])
255 RETURNS integer
256 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'cdata_typmodin'
257 LANGUAGE 'C' IMMUTABLE STRICT;
259 CREATE OR REPLACE FUNCTION CData_typmodout(integer)
260 RETURNS cstring
261 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'cdata_typmodout'
262 LANGUAGE 'C' IMMUTABLE STRICT;
264 CREATE TYPE CData (
265 INTERNALLENGTH = 24,
266 INPUT = CData_in,
267 OUTPUT = CData_out,
268 TYPMOD_IN = CData_typmodin,
269 TYPMOD_OUT = CData_typmodout,
270 ALIGNMENT = double,
271 STORAGE = plain
272 );
274 CREATE OR REPLACE FUNCTION CData(cdata, integer, boolean)
275 RETURNS cdata
276 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'cdata_to_cdata'
277 LANGUAGE 'C' IMMUTABLE STRICT;
279 CREATE CAST (cdata AS cdata)
280 WITH FUNCTION CData(cdata, integer, boolean)
281 AS IMPLICIT;
283 CREATE CAST (numeric AS cdata)
284 WITH INOUT
285 AS ASSIGNMENT;
287 CREATE OR REPLACE FUNCTION CData(integer, integer, boolean)
288 RETURNS cdata
289 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'int32_to_cdata'
290 LANGUAGE 'C' IMMUTABLE STRICT;
292 CREATE CAST (integer AS cdata)
293 WITH FUNCTION CData(integer, integer, boolean)
294 AS ASSIGNMENT;
296 CREATE OR REPLACE FUNCTION Float8(cdata)
297 RETURNS double precision
298 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'cdata_to_float8'
299 LANGUAGE 'C' IMMUTABLE STRICT;
301 CREATE CAST (cdata AS double precision)
302 WITH FUNCTION Float8(cdata);
303 -- EXPLICIT
305 CREATE OR REPLACE FUNCTION CData_update(cdata, cdata)
306 RETURNS cdata
307 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'cdata_update'
308 LANGUAGE 'C' IMMUTABLE;
310 CREATE OR REPLACE FUNCTION PostRR_update(name, name, name, timestamptz, double precision)
311 RETURNS cdata
312 LANGUAGE plpgsql
313 AS $$
314 DECLARE
315 tbl ALIAS FOR $1;
316 tscol ALIAS FOR $2;
317 vcol ALIAS FOR $3;
318 ts ALIAS FOR $4;
319 value ALIAS FOR $5;
320 ts_str text;
321 v_str text;
322 update_qry text;
323 newts rrtimeslice;
324 new cdata;
325 BEGIN
326 tscol := quote_ident(tscol);
327 vcol := quote_ident(vcol);
328 ts_str := quote_literal(ts);
329 v_str := quote_literal(value);
331 update_qry = 'CData_update(' || vcol || ', ' || v_str || ')';
333 BEGIN
334 EXECUTE 'UPDATE ' || tbl
335 || ' SET ' || tscol || ' = ' || ts_str
336 || ', ' || vcol || ' = ' || update_qry
337 || ' WHERE ' || tscol || ' = ' || ts_str
338 || ' RETURNING ' || tscol || ', ' || vcol
339 INTO STRICT newts, new;
341 EXCEPTION
342 WHEN NO_DATA_FOUND THEN
343 EXECUTE 'INSERT INTO ' || tbl
344 || ' (' || tscol || ', ' || vcol
345 || ') VALUES (' || ts_str || ', ' || v_str
346 || ') RETURNING ' || tscol || ', ' || vcol
347 INTO newts, new;
348 -- use strict again; on exception retry?
349 WHEN TOO_MANY_ROWS THEN
350 RAISE EXCEPTION '% is not unique in %.%', ts_str, tbl, tscol;
351 END;
352 RETURN new;
353 END;
354 $$;
356 CREATE OR REPLACE FUNCTION PostRR_update(text, timestamptz, double precision)
357 RETURNS SETOF cdata
358 LANGUAGE plpgsql
359 AS $$
360 DECLARE
361 -- $1: rraname
362 -- $2: timestamp
363 -- $3: value
364 adef RECORD;
365 new cdata;
366 BEGIN
367 FOR adef IN SELECT tbl, tscol, vcol FROM postrr.rrarchives
368 WHERE postrr.rrarchives.rraname = $1 LOOP
369 SELECT PostRR_update(adef.tbl, adef.tscol, adef.vcol, $2, $3)
370 INTO new;
371 RETURN NEXT new;
372 END LOOP;
373 RETURN;
374 END;
375 $$;
377 COMMIT;
379 SET client_min_messages TO DEFAULT;
381 -- vim: set tw=78 sw=4 ts=4 noexpandtab :