X-Git-Url: https://git.verplant.org/?a=blobdiff_plain;f=contrib%2Fpostgresql%2Fcollectd_insert.sql;h=bee182cb715f703605c7983a2cccbb2c12f3af37;hb=b44bcea65c45e390a0d5b53377e85ca8e76665cf;hp=7ccd909a7a2feffcee87f7aee580b5ae17fb6340;hpb=ad0244784f7a9bb3a4ebbb992401a16b0c75a1b6;p=collectd.git

diff --git a/contrib/postgresql/collectd_insert.sql b/contrib/postgresql/collectd_insert.sql
index 7ccd909a..bee182cb 100644
--- a/contrib/postgresql/collectd_insert.sql
+++ b/contrib/postgresql/collectd_insert.sql
@@ -33,8 +33,8 @@
 -- and 'values' to store the value-list identifier and the actual values
 -- respectively.
 --
--- The 'values' table is partitioned to improve performance and maintainance.
--- Please note that additional maintainance scripts are required in order to
+-- The 'values' table is partitioned to improve performance and maintenance.
+-- Please note that additional maintenance scripts are required in order to
 -- keep the setup running -- see the comments below for details.
 --
 -- The function 'collectd_insert' may be used to actually insert values
@@ -85,7 +85,7 @@ CREATE INDEX identifiers_type_inst ON identifiers USING btree (type_inst);
 
 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
 );
@@ -104,7 +104,7 @@ CREATE OR REPLACE VIEW collectd
                     WHEN type_inst IS NOT NULL THEN '-'
                     ELSE ''
                 END
-                || coalesce(plugin_inst, '') AS identifier,
+                || coalesce(type_inst, '') AS identifier,
             tstamp, name, value
         FROM identifiers
             JOIN values
@@ -117,7 +117,7 @@ CREATE OR REPLACE VIEW collectd
 -- 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
@@ -125,14 +125,12 @@ 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;
 
@@ -150,8 +148,7 @@ BEGIN
             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"
@@ -160,7 +157,7 @@ BEGIN
             ADD CONSTRAINT "values_' || cur_day || '_id_fkey"
                 FOREIGN KEY (id) REFERENCES identifiers(id)';
     END LOOP;
-    RETURN n;
+    RETURN;
 END;
 $_$;
 
@@ -191,7 +188,7 @@ CREATE TRIGGER insert_values_trigger
 -- 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[]