collectd_insert.sql: Added view 'collectd'.

This view provides a join of the 'identifiers' and 'values' tables plus an
additional column with the serialized collectd identifier.

diff --git a/contrib/postgresql/collectd_insert.sql b/contrib/postgresql/collectd_insert.sql
index e20b0bf1e64b1ab3f3024c936f0e3db8dc9484fb..7ccd909a7a2feffcee87f7aee580b5ae17fb6340 100644 (file)
--- a/contrib/postgresql/collectd_insert.sql
+++ b/contrib/postgresql/collectd_insert.sql
@@ -90,6 +90,26 @@ CREATE TABLE "values" (
     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: