3

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.

2 Answers 2

2

Before going into details - are you sure you are not re-inventing a wheel? This might burn down to the very popular topic of .

Your query:

SELECT min(value) as value, valued_min(value, name) as name FROM kv;

Can be rewritten with stock Postgres as:

SELECT value, name
FROM   kv
ORDER  BY value
LIMIT  1;

Which also can use a simple btree index on (value) or (value, name) for an index or index-only scan - much faster.

I am pretty sure any other example can be solved with built-in functionality as well. To get one row per group, your query would be:

SELECT grp_col, min(value) AS value, valued_min(value, name) AS name
FROM   kv
GROUP  BY grp_col;

Replace with:

SELECT DISTINCT ON (grp_col)
       grp_col, value, name
FROM   kv
ORDER  BY grp_col, value;

Again, faster. And much more versatile. Detailed explanation:

2
  • The greatest-n-per-group is a good keyword. Thx. My query is just an example so the first solution is not possible for me (I need more than just one value). The other query on the other hand is a good suggestion, but I'm affraid that I cannot avoid the group by clause as I have a TSDB and want to draw a min, max, avg/mean graph (min and max show the entries's name). I'll play with distinct a bit to check whether I can use it in that case as well. I omited the max/avg function to not bloat the question with to much SQL.
    – ST-DDT
    Aug 6, 2018 at 8:17
  • I see. There are also window functions ... Maybe you post an actual example of your query as new question. (Don't overload this one.) I am still confident it can be solved without custom aggregate function. Even if the query might be more verbose, index access is the key to performance with big tables. Aug 6, 2018 at 12:37
0

Sorry, I can not comment! < 50 reputation
Please make this a comment under the accepted answer

follow-up question: Aggregate additional values to min and max

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.