Promscale API Extras

prom_api_extras.pgsql

-- PL/PgSQL Functions for Promscale API Schema
-- Copyright (C) 2021 Matous Jan Fialka, <https://mjf.cz/>
-- Released under the terms of "The MIT License"

-- LABEL VALUES FUNCTIONS
--
-- label_values([<metric>,] <label>[, <time from>[, <time to>]])
--
-- Where:
--   <metric>    is name of a metric
--   <label>     is name of a metric label
--   <time from> is either SQL TIMESTAMPTZ or INTEGER (unix timestamp)
--   <time to>   is either SQL TIMESTAMPTZ or INTEGER (unix timestamp)
--
-- Example:
--   SELECT *
--     FROM label_values('metric', 'label', 1609459200, 1609462800)
--    WHERE value NOT LIKE 'something%';
--
-- Warning:
--   Using <time from> and <time to> may be slow for large ranges.

-- Function label_values(<label>)

CREATE OR REPLACE FUNCTION prom_api.label_values (
    IN _label TEXT
)
RETURNS TABLE (
    value TEXT
)
AS $function$
BEGIN
    RETURN QUERY EXECUTE FORMAT(
    $sql$
    SELECT value
      FROM label
     WHERE key = '%s';
    $sql$, _label);
    IF NOT FOUND THEN
        RAISE EXCEPTION 'No value for any metric with label "%".',
            _label;
    END IF;
    RETURN;
END;
$function$ STABLE PARALLEL SAFE LANGUAGE plpgsql;

-- Function label_values(<metric>, <label>)

CREATE OR REPLACE FUNCTION prom_api.label_values (
    IN _metric TEXT,
    IN _label  TEXT
)
RETURNS TABLE (
    value TEXT
)
AS $function$
BEGIN
    RETURN QUERY EXECUTE FORMAT(
    $sql$
    SELECT l.value "%s"
      FROM label_key_position p
           JOIN (
                  SELECT key,
                         value
                    FROM label
                   WHERE key = '%s'
                ) l
             ON p.key = l.key AND
                p.metric_name = '%s';
    $sql$, _label, _label, _metric);
    IF NOT FOUND THEN
        RAISE EXCEPTION 'No value for metric "%" and label "%".',
            _metric, _label;
    END IF;
    RETURN;
END;
$function$ STABLE PARALLEL SAFE LANGUAGE plpgsql;

-- Function label_values(<metric>, <label>, <time from>)

CREATE OR REPLACE FUNCTION prom_api.label_values (
    IN _metric    TEXT,
    IN _label     TEXT,
    IN _time_from TIMESTAMP WITH TIME ZONE
)
RETURNS TABLE (
    value TEXT
)
AS $function$
BEGIN
    RETURN QUERY EXECUTE FORMAT(
    $sql$
    SELECT value "%s"
      FROM label
     WHERE key = '%s' AND
           id IN (
                   SELECT UNNEST(labels)
                     FROM prom_data_series."%s" s
                          INNER JOIN (
                                       SELECT series_id
                                         FROM prom_data."%s"
                                        WHERE time >= '%s'
                                        GROUP BY 1
                                     ) a
                          ON s.id = a.series_id
                    GROUP BY 1
                 );
    $sql$, _label, _label, _metric, _metric, _time_from);
    IF NOT FOUND THEN
        RAISE EXCEPTION 'No value for metric "%" and label "%" since "%".',
            _metric, _label, _time_from;
    END IF;
    RETURN;
END;
$function$ STABLE PARALLEL SAFE LANGUAGE plpgsql;

-- Function label_values(<metric>, <label>, <time from>) - unix timestamp

CREATE OR REPLACE FUNCTION prom_api.label_values (
    IN _metric    TEXT,
    IN _label     TEXT,
    IN _time_from INTEGER
)
RETURNS TABLE (
    value TEXT
)
AS $function$
DECLARE
    _ts_from TIMESTAMP WITH TIME ZONE;
BEGIN
    SELECT to_timestamp(_time_from) INTO _ts_from;
    RETURN QUERY EXECUTE FORMAT(
    $sql$
    SELECT value "%s"
      FROM label
     WHERE key = '%s' AND
           id IN (
                   SELECT UNNEST(labels)
                     FROM prom_data_series."%s" s
                          INNER JOIN (
                                       SELECT series_id
                                         FROM prom_data."%s"
                                        WHERE time >= '%s'
                                        GROUP BY 1
                                     ) a
                          ON s.id = a.series_id
                    GROUP BY 1
                 );
    $sql$, _label, _label, _metric, _metric, _ts_from);
    IF NOT FOUND THEN
        RAISE EXCEPTION 'No value for metric "%" and label "%" since unix timestamp "%".',
            _metric, _label, _time_from;
    END IF;
    RETURN;
END;
$function$ STABLE PARALLEL SAFE LANGUAGE plpgsql;

-- Function label_values(<metric>, <label>, <time from>, <time to>)

CREATE OR REPLACE FUNCTION prom_api.label_values (
    IN _metric    TEXT,
    IN _label     TEXT,
    IN _time_from TIMESTAMP WITH TIME ZONE,
    IN _time_to   TIMESTAMP WITH TIME ZONE
)
RETURNS TABLE (
    value TEXT
)
AS $function$
BEGIN
    RETURN QUERY EXECUTE FORMAT(
    $sql$
    SELECT value "%s"
      FROM label
     WHERE key = '%s' AND
           id IN (
                   SELECT UNNEST(labels)
                     FROM prom_data_series."%s" s
                          INNER JOIN (
                                       SELECT series_id
                                         FROM prom_data."%s"
                                        WHERE time >= '%s' AND
                                              time <  '%s'
                                        GROUP BY 1
                                     ) a
                          ON s.id = a.series_id
                    GROUP BY 1
                 );
    $sql$, _label, _label, _metric, _metric, _time_from, _time_to);
    IF NOT FOUND THEN
        RAISE EXCEPTION 'No value for metric "%" and label "%" within time range "%".."%".',
            _metric, _label, _time_from, _time_to;
    END IF;
    RETURN;
END;
$function$ STABLE PARALLEL SAFE LANGUAGE plpgsql;

-- Function label_values(<metric>, <label>, <time from>, <time to>) - unix timestamp

CREATE OR REPLACE FUNCTION prom_api.label_values (
    IN _metric    TEXT,
    IN _label     TEXT,
    IN _time_from INTEGER,
    IN _time_to   INTEGER
)
RETURNS TABLE (
    value TEXT
)
AS $function$
DECLARE
    _ts_from TIMESTAMP WITH TIME ZONE;
    _ts_to   TIMESTAMP WITH TIME ZONE;
BEGIN
    SELECT to_timestamp(_time_from) INTO _ts_from;
    SELECT to_timestamp(_time_to)   INTO _ts_to;
    RETURN QUERY EXECUTE FORMAT(
    $sql$
    SELECT value "%s"
      FROM label
     WHERE key = '%s' AND
           id IN (
                   SELECT UNNEST(labels)
                     FROM prom_data_series."%s" s
                          INNER JOIN (
                                       SELECT series_id
                                         FROM prom_data."%s"
                                        WHERE time >= '%s' AND
                                              time <  '%s'
                                        GROUP BY 1
                                     ) a
                          ON s.id = a.series_id
                    GROUP BY 1
                 );
    $sql$, _label, _label, _metric, _metric, _ts_from, _ts_to);
    IF NOT FOUND THEN
        RAISE EXCEPTION 'No value for metric "%" and label "%" within unix timestamp range "%".."%".',
            _metric, _label, _time_from, _time_to;
    END IF;
    RETURN;
END;
$function$ STABLE PARALLEL SAFE LANGUAGE plpgsql;

-- vi:ft=pgsql