Recently I wrote a custom aggregate function in postgres that would return a specific column for the row that matches the max
/min
aggregate using a different column.
While the code in itself works great it is somewhat bothersome to create custom data type for every possible input combination that I might need.
Here is the code I use
CREATE TYPE agg_tuple_text AS
(
exists boolean,
value numeric,
text text
);
--------------------------------------------------------------------------------
CREATE FUNCTION valued_min(old_tuple agg_tuple_text, new_value numeric, new_text text)
RETURNS agg_tuple_text
LANGUAGE plpgsql
AS $$
BEGIN
IF (old_tuple).exists = false THEN
RETURN (true, new_value, new_text);
ELSIF (old_tuple).value > new_value THEN
RETURN (true, new_value, new_text);
ELSE
RETURN old_tuple;
END IF;
END;
$$;
--------------------------------------------------------------------------------
CREATE FUNCTION unpack_agg_tuple_text(value agg_tuple_text)
RETURNS text
LANGUAGE plpgsql
AS $$
BEGIN
IF (value).exists = false THEN
RETURN NULL;
ELSE
RETURN (value).text;
END IF;
END
$$;
--------------------------------------------------------------------------------
CREATE AGGREGATE valued_min(numeric, text)
(
INITCOND = '(false, 0, null)',
STYPE = agg_tuple_text,
SFUNC = valued_min,
FINALFUNC = unpack_agg_tuple_text
);
--------------------------------------------------------------------------------
-- Example
SELECT min(value) as min_value, valued_min(value, name) as min_name, max..., avg... FROM kv;
-- Output:
-- min_value | min_name | ...
-- ----------+--------------------+----
-- 11.11 | this is the lowest | ...
EDIT: My goal is drawing a min/max/avg chart for a TSDB and displaying the name of the min and max entries each.
Is there a way to achieve this without creating all of these for every possible combination? (Maybe some kind of generic parameter that are present in Java or alike)
- Value column types
- Various Date/Time types
- Numeric types
- Maybe text
- (any comparable type)
- data column types
- anytype
It would be sufficient if I only could use it for the data value since it isn't used in any calculation inside that code. Unfortunately the anyelement type isn't allowed in custom data types.
I already considered using the json type as input, but that feels somewhat wrong, because it looses the type information (especially for date/time types).
I use Postgres 10 without extensions, but if this is possible using postgres 1x or using a special extension I'm willing to try.
I also considered joining the values, but then I get isues with performance and potential duplicates/rows that have the same value.