Thread: selecting a materialized view function, plpgsql

Hello. 

I am using timescaledb with grafana. I am at a point where my telemetry data is very large. I collect a metric every 1 second so per 1 day I have close to (3600*24), 86400 points. Graphing them on grafana has become a challenge because postgresql can't keep up. Especially when I want to view a 7 day or 30 day summary. 

To fix this, I created materialized views and they seem to be working but its quite error prone when I try to implement this in Grafana. So I decided to take the plpgsql route. 

I have a function which looks like this

create or replace function nview(from_millis BIGINT,to_millis BIGINT)

RETURNS SETOF record as $$

DECLARE

  day_diff INTEGER;

  query TEXT;

BEGIN

  day_diff := (to_millis - from_millis) / (1000*3600*24);

  IF day_diff >=10 THEN

     -- run query which has 1 day averages

  ELSIF day_diff BETWEEN 4 AND 10 THEN

    -- run query which has 6 hour summaries

  ELSE

    -- run whatever

  END IF;

RETURN QUERY EXECUTE query;

END;

$$ LANGUAGE plpgsql;

Was wondering is this a good approach? Have anyone else used Grafana with Postgresql?