Is there a way to use functions that can take multiple columns as input (e.g. `GREATEST`) without ty-CodePudding

I have a table with a couple of 100 columns on which I want to use the GREATEST function. How can I prevent putting each and every column name into the query?

The columns on which I want to use GREATEST have a common prefix in case this might help.

That's how I am doing it now.

CREATE TEMP TABLE foo (id int, cc_a int, cc_b int, cc_c int);
INSERT INTO foo VALUES (3, '1', '2', '3'), (4, '15', '4', '100');

SELECT id, GREATEST(cc_a, cc_b, cc_c) FROM diff_table

CodePudding user response：

You can turn a row into a list of values using e.g. JSON functions:

SELECT id, 
       (select max(x.val::int)
       from jsonb_each(to_jsonb(f) - 'id') as x(col, val))
from foo f

Online example

But I do agree that this smells like a bad design.

CodePudding user response：

Since you want to operate on a large number of values per id, it's time to bite the bullet and create an UNPIVOTed view of your table.

Con : you have to type the hundred values
Pro : you only have to it once in your code base

I know you are working on PostgreSQL, so there are solutions such as the hstore based solution which according to the answerer, may work on an arbitrary large number of columns.

if you can't use the hstore extension - it's not installed on dbfiddle.com. then you can fall back on LATERAL JOIN, inspired by https://blog.sql-workbench.eu/post/unpivot-with-postgres/. I tested a solution in this fiddle