I am trying to write a function which does (some complex stuff) and returns the results in different orders based on a parameter.
A simplified version would look something like this:
CREATE OR REPLACE FUNCTION test(order_column text) RETURNS TABLE(thing1 bigint,thing2 text, thing3 timestamp without time zone) LANGUAGE 'plpgsql' AS $BODY$ BEGIN RETURN QUERY SELECT thing1, thing2::text, thing3 FROM some_table ORDER BY CASE WHEN order_column='id' THEN thing1 ELSE thing3 END DESC; END; $BODY$;
Unfortunately, thing1 is a bigint and thing3 is a timestamp, and when I try to run the function I get an error saying bigint and timestamp types can't be matched, which I interpret as saying that the types returned from the case need to be the same (or at least compatible). I can't cast them both to text, because the range of values don't sort correctly then.
I've tried returning the column numbers instead of the column names - this at least executes, but it ignores the column order (in the function or just executing as a simple statement). For example,
SELECT * FROM some_table ORDER BY 1;
works correctly but
SELECT * FROM some_table ORDER BY CASE WHEN TRUE THEN 1 ELSE 2 END;
does not order by column 1
My work-around would be to do
if column_order='first' then (masses of complex stuff) SELECT ... ORDER BY thing1 else (masses of complex stuff, duplicated) SELECT ... ORDER BY thing3 end if;
but that's horrible, and I'm really hoping there's some other way around this, and that I'm currently missing something.
Is there any way to do what I'm trying to do?