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?
ORDER BY CASE WHEN order_column='id' THEN thing1 END DESC, thing3 DESC