Re: Dynamic expressions set in "order by" clause

> Not that I've ever heard of. Well, you move the procedure up into
> the application level but inside the database you'll need a procedure
> to handle the branching on the argument values with each branch

The thing is that it's internal query and its result isn't retuned
from the procedure.

> You could write a set of expressions that yield proper order by field
> in one case and say null in another. Say we want order by columns
> <col1, col2 desc> if the first function argument is '1' and by <col3
> desc, col1, col2> if it is '2', this can be achieved as:

Thank you for your suggestion but unfortunately it doesn't cover whole
the problem. There are a lot of conditions in my query I have to check
to define the set of ordering fields/expressions so it's extremely
hard to do and debug.

Today I've come to a solution but I need help of gurus. Let me try to
explain it.

If all the columns we need to sort by were the same type (for example
float8) we would be able to form a sorting fields array and sort by
it. Instead of "desc" we would be able to use "-" operator.

select * from (
values
(1.27, 23.46, 56.2, 76.1),
(4.35, 6.76, 45.3, 1.6)
) as sq
order by
case
when false then array[-column1, column3]
when true then array[column1]
else array[column2, -column4, column1]
end

So the only we need is any_type_to_float8_mapping functions family. I
digged Google for the solution and have found convert_to_scalar()
function in selfuncs.c. Also I've found mention about a patch witch
provide this function to be used from SQL
(http://www.postgresql.org/community/weeklynews/pwn20070805.html)

The question is how can I use it from SQL it it's worth doing and/or
is there another solutions to do such mapping?

--
Regards,
Sergey Konoplev