0

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?

3
  • 1
    Try: ORDER BY CASE WHEN order_column='id' THEN thing1 END DESC, thing3 DESC – ypercubeᵀᴹ Sep 21 '20 at 10:33
  • Of course! Treating them as separate column rather than alternates for the same column - talk about not seeing the forest for the trees, thanks for that – khafka Sep 21 '20 at 10:54
  • Also check the performance aspect. Your "ugly code" or some similar dynamic SQL construct might perform better. – ypercubeᵀᴹ Sep 21 '20 at 10:57
0

Be careful with conditional ordering, it can create bad query plans sometimes forcing table scanning. If the filtering and joining clauses, or just the size of the actual data, mean that you have a small number of rows to sort at the end then this is not an issue and something like this will work:

ORDER BY CASE WHEN ordering_column = 'id'        THEN id        ELSE NULL END
       , CASE WHEN ordering_column = 'timestamp' THEN timestamp ELSE NULL END

In fact it will work anyway, it might just be inefficient for a large amount of data.

For larger outputs your workaround may be more efficient as it may be able to make better use of indexes for the sorting. Another alternative is to have two procedures, one for each sort, and either call each as needed or have your main procedure call the others depending on the sort order it is passed in the parameter. Depending on how postgres handles cached query plans for procedures this may[†] avoid issues of a cached plan for one case being used for another where it is vastly less efficient.

[†] I'm no expert at all on pg's internals, but single "kitchen sink" procedures and queries with conditional sorts etc. can be a performance killer in SQL Server for this sort of reason.

2
  • That's the way I coded it in the end, as it was a bit more explicit how it was working. The ordering is happening at the end of a chain of CTEs that reduce the (large) set of records down based on a set of conditions, to a more manageable final set which then needs to be ordered. None of it is really ideal, but the ordering is probably the least performance critical part of it – khafka Sep 21 '20 at 12:22
  • "a bit more explicit how it was working" - sometimes if the performance difference is small/nothing then that is the way to go. Easier to maintain so may save you bug related headache later. – David Spillett Sep 21 '20 at 16:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.