Create an aggregate function which returns the column1 value associated to the maximum column2 value

Question

I need to create an aggregate function. For example, MAX(column) returns the max column values from the parsed rows. My new function LAST_ALERT_VALUE(column) should return the column value of the row which has the biggest reception_time.

Ex: if i have:

| severity       | reception_time          |
|----------------+-------------------------|
| 1              + 2016-07-04 00:00:00.000 |
| 3              + 2016-09-04 00:00:00.000 |
| 4              + 2016-08-04 00:00:00.000 |
| 2              + 2016-11-04 00:00:00.000 |
| 5              + 2016-10-04 00:00:00.000 |

then LAST_ALERT_VALUE(severity) should return 2

I Here is my code:

CREATE OR REPLACE FUNCTION max_reception_time(time1 anyelement, time2 anyelement) RETURNS anyelement AS $$
BEGIN
if time1 > time2 then
    return time1;
end if;
return time2;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION last_alert_value_func(p_reception_time anyelement) RETURNS anyelement AS $$
BEGIN
SELECT severity FROM report.report_table AS r WHERE r.reception_time = p_reception_time;
END;
$$ LANGUAGE plpgsql;


CREATE AGGREGATE last_alert_value(anyelement)
(
    sfunc = max_reception_time,
    stype = anyelement,
    finalfunc = last_alert_value_func
);

select last_alert_value(severity) from report.report_table;

I have the error: ERROR: operator does not exist: timestamp without time zone = alertseverityenum

How can I make last_alert_value(severity) works ? I also want to can give as argument others columns to last_alert_value, how can I do that ?

Answer 1

Your aggregate is pointless because you can just

select severity
from report_table
order by reception_time desc
limit 1;

Assuming this is only an example of a custom aggregate with more than one argument, a solution may look like this:

create or replace function max_reception_time(state_rec report_table, severity int, reception_time timestamp) 
returns report_table language sql as $$
    select case 
        when state_rec.reception_time > reception_time
        then state_rec
        else (severity, reception_time)::report_table
        end;
$$;

create or replace function max_reception_time_final(state_rec report_table)
returns int language sql as $$
    select state_rec.severity
$$;

create aggregate last_alert_value(severity int, reception_time timestamp)
(
    sfunc = max_reception_time,
    stype = report_table,
    finalfunc = max_reception_time_final
);

select last_alert_value(severity, reception_time)
from report_table t;

Note, that I have used report_table as the state data type. You could create a composite type instead.

Working code in rextester.