CREATE TABLE "values" (
id integer NOT NULL,
- tstamp timestamp without time zone NOT NULL,
+ tstamp timestamp with time zone NOT NULL,
name character varying(64) NOT NULL,
value double precision NOT NULL
);
+CREATE OR REPLACE VIEW collectd
+ AS SELECT host, plugin, plugin_inst, type, type_inst,
+ host
+ || '/' || plugin
+ || CASE
+ WHEN plugin_inst IS NOT NULL THEN '-'
+ ELSE ''
+ END
+ || coalesce(plugin_inst, '')
+ || '/' || type
+ || CASE
+ WHEN type_inst IS NOT NULL THEN '-'
+ ELSE ''
+ END
+ || coalesce(plugin_inst, '') AS identifier,
+ tstamp, name, value
+ FROM identifiers
+ JOIN values
+ ON values.id = identifiers.id;
+
-- partition "values" by day (or week, month, ...)
-- create the child tables for today and the next 'days' days:
-- the tables for the next couple of days
CREATE OR REPLACE FUNCTION values_update_childs(
integer
- ) RETURNS integer
+ ) RETURNS SETOF text
LANGUAGE plpgsql
AS $_$
DECLARE
cur_day date;
next_day date;
i integer;
- n integer;
BEGIN
IF days < 1 THEN
RAISE EXCEPTION 'Cannot have negative number of days';
END IF;
i := 0;
- n := 0;
LOOP
EXIT WHEN i > days;
CONTINUE;
END;
- RAISE INFO 'Created table "values$%"', cur_day;
- n := n + 1;
+ RETURN NEXT 'values$' || cur_day::text;
EXECUTE 'ALTER TABLE ONLY "values$' || cur_day || '"
ADD CONSTRAINT "values_' || cur_day || '_pkey"
ADD CONSTRAINT "values_' || cur_day || '_id_fkey"
FOREIGN KEY (id) REFERENCES identifiers(id)';
END LOOP;
- RETURN n;
+ RETURN;
END;
$_$;
-- SET constraint_exclusion = on;
CREATE OR REPLACE FUNCTION collectd_insert(
- timestamp, character varying,
+ timestamp with time zone, character varying,
character varying, character varying,
character varying, character varying,
character varying[], character varying[], double precision[]