From ad0244784f7a9bb3a4ebbb992401a16b0c75a1b6 Mon Sep 17 00:00:00 2001 From: Sebastian Harl Date: Thu, 25 Oct 2012 12:37:02 +0200 Subject: [PATCH] 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. --- contrib/postgresql/collectd_insert.sql | 20 ++++++++++++++++++++ 1 file changed, 20 insertions(+) diff --git a/contrib/postgresql/collectd_insert.sql b/contrib/postgresql/collectd_insert.sql index e20b0bf1..7ccd909a 100644 --- 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: -- 2.11.0