Postgres: how can I aggregate a row to report the column name with the highest value?

Question

For each row in a postgres table, I'm trying to determine the name of the column with the highest value:

input

 row_id | type1 | type2 | type3 
--------+-------+-------+-------
 A      |     2 |    44 |     7
 B      |   321 |     5 |    73
 C      |   302 |   620 |     9

desired output

 row_id | max_type | max_value 
--------+----------+-----------
 A      | type2    |        44
 B      | type1    |       321
 C      | type2    |       620

This is similar to this SQL Server question, but CROSS APPLY seems to not be precisely related to anything in postgres.

I've tried this, but it traverses the entire table, not one row at a time:

SELECT
  unnest(ARRAY['type1','type2','type3']) AS max_type,
  unnest(ARRAY[type1,type2,type3]) AS max_value
FROM table
ORDER BY max_value DESC
LIMIT 1;

What's the correct way to go about this in postgres?

Answer 1

Use DISTINCT ON (row_id) with ORDER BY row_id, max_value DESC:

SELECT DISTINCT ON (row_id) row_id, max_type, max_value
FROM (
    SELECT
        row_id,
        unnest(ARRAY['type1','type2','type3']) AS max_type,
        unnest(ARRAY[type1,type2,type3]) AS max_value
    FROM a_table
    ) s
ORDER BY row_id, max_value DESC;

 row_id | max_type | max_value 
--------+----------+-----------
 A      | type2    |        44
 B      | type1    |       321
 C      | type2    |       620
(3 rows)