bc0d20af0fd810b5f659703abeac4c68786d0cba
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 OR REPLACE FUNCTION PostRR_Version()
49 RETURNS cstring
50 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'postrr_version'
51 LANGUAGE 'C' IMMUTABLE;
53 CREATE TYPE RRTimeslice;
55 CREATE OR REPLACE FUNCTION RRTimeslice_validate(integer)
56 RETURNS cstring
57 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_validate'
58 LANGUAGE 'C' IMMUTABLE STRICT;
60 -- this will abort the transaction in case the expected internal length does
61 -- not match the actual length
62 SELECT RRTimeslice_validate(16);
64 CREATE OR REPLACE FUNCTION RRTimeslice_in(cstring, oid, integer)
65 RETURNS RRTimeslice
66 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_in'
67 LANGUAGE 'C' IMMUTABLE STRICT;
69 CREATE OR REPLACE FUNCTION RRTimeslice_out(RRTimeslice)
70 RETURNS cstring
71 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_out'
72 LANGUAGE 'C' IMMUTABLE STRICT;
74 CREATE OR REPLACE FUNCTION RRTimeslice_typmodin(cstring[])
75 RETURNS integer
76 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_typmodin'
77 LANGUAGE 'C' IMMUTABLE STRICT;
79 CREATE OR REPLACE FUNCTION RRTimeslice_typmodout(integer)
80 RETURNS cstring
81 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_typmodout'
82 LANGUAGE 'C' IMMUTABLE STRICT;
84 CREATE TYPE RRTimeslice (
85 INTERNALLENGTH = 16,
86 INPUT = RRTimeslice_in,
87 OUTPUT = RRTimeslice_out,
88 TYPMOD_IN = RRTimeslice_typmodin,
89 TYPMOD_OUT = RRTimeslice_typmodout,
90 ALIGNMENT = double,
91 STORAGE = plain
92 );
94 CREATE OR REPLACE FUNCTION RRTimeslice(rrtimeslice, integer, boolean)
95 RETURNS rrtimeslice
96 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_to_rrtimeslice'
97 LANGUAGE 'C' IMMUTABLE STRICT;
99 CREATE CAST (rrtimeslice AS rrtimeslice)
100 WITH FUNCTION RRTimeslice(rrtimeslice, integer, boolean)
101 AS IMPLICIT;
103 CREATE OR REPLACE FUNCTION RRTimeslice_eq(rrtimeslice, rrtimeslice)
104 RETURNS boolean
105 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_eq'
106 LANGUAGE 'C' IMMUTABLE STRICT;
108 CREATE OR REPLACE FUNCTION RRTimeslice_ne(rrtimeslice, rrtimeslice)
109 RETURNS boolean
110 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_ne'
111 LANGUAGE 'C' IMMUTABLE STRICT;
113 CREATE OR REPLACE FUNCTION RRTimeslice_lt(rrtimeslice, rrtimeslice)
114 RETURNS boolean
115 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_lt'
116 LANGUAGE 'C' IMMUTABLE STRICT;
118 CREATE OR REPLACE FUNCTION RRTimeslice_le(rrtimeslice, rrtimeslice)
119 RETURNS boolean
120 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_le'
121 LANGUAGE 'C' IMMUTABLE STRICT;
123 CREATE OR REPLACE FUNCTION RRTimeslice_gt(rrtimeslice, rrtimeslice)
124 RETURNS boolean
125 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_gt'
126 LANGUAGE 'C' IMMUTABLE STRICT;
128 CREATE OR REPLACE FUNCTION RRTimeslice_ge(rrtimeslice, rrtimeslice)
129 RETURNS boolean
130 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_ge'
131 LANGUAGE 'C' IMMUTABLE STRICT;
133 CREATE OR REPLACE FUNCTION RRTimeslice_cmp(rrtimeslice, rrtimeslice)
134 RETURNS integer
135 AS 'postrr-@POSTRR_MAJOR_VERSION@.@POSTRR_MINOR_VERSION@', 'rrtimeslice_cmp'
136 LANGUAGE 'C' IMMUTABLE STRICT;
138 CREATE OPERATOR = (
139 LEFTARG = RRTimeslice,
140 RIGHTARG = RRTimeslice,
141 PROCEDURE = RRTimeslice_eq,
142 COMMUTATOR = =,
143 NEGATOR = <>,
144 RESTRICT = eqsel
145 );
147 CREATE OPERATOR <> (
148 LEFTARG = RRTimeslice,
149 RIGHTARG = RRTimeslice,
150 PROCEDURE = RRTimeslice_ne,
151 COMMUTATOR = <>,
152 NEGATOR = =,
153 RESTRICT = neqsel
154 );
156 CREATE OPERATOR < (
157 LEFTARG = RRTimeslice,
158 RIGHTARG = RRTimeslice,
159 PROCEDURE = RRTimeslice_lt,
160 COMMUTATOR = >,
161 NEGATOR = <=,
162 RESTRICT = scalarltsel
163 );
165 CREATE OPERATOR <= (
166 LEFTARG = RRTimeslice,
167 RIGHTARG = RRTimeslice,
168 PROCEDURE = RRTimeslice_le,
169 COMMUTATOR = >=,
170 NEGATOR = <,
171 RESTRICT = scalarltsel
172 );
174 CREATE OPERATOR > (
175 LEFTARG = RRTimeslice,
176 RIGHTARG = RRTimeslice,
177 PROCEDURE = RRTimeslice_gt,
178 COMMUTATOR = <,
179 NEGATOR = >=,
180 RESTRICT = scalargtsel
181 );
183 CREATE OPERATOR >= (
184 LEFTARG = RRTimeslice,
185 RIGHTARG = RRTimeslice,
186 PROCEDURE = RRTimeslice_ge,
187 COMMUTATOR = <=,
188 NEGATOR = >,
189 RESTRICT = scalargtsel
190 );
192 CREATE OPERATOR CLASS rrtimeslice_ops
193 DEFAULT FOR TYPE RRTimeslice USING btree AS
194 OPERATOR 1 < ,
195 OPERATOR 2 <= ,
196 OPERATOR 3 = ,
197 OPERATOR 4 >= ,
198 OPERATOR 5 > ,
199 FUNCTION 1 RRTimeslice_cmp(rrtimeslice, rrtimeslice);
201 COMMIT;
203 SET client_min_messages TO DEFAULT;
205 -- vim: set tw=78 sw=4 ts=4 noexpandtab :