Thread: Dynamic expressions set in "order by" clause

Hello,

I have a procedure which takes few arguments. The arguments must
affect expressions set of "order by" clause. For instance in one case
ordering must be performed by 1st, 2nd (desc) and 3rd fields but in
another by 3rd, 1st and 2nd fields.

Is there a way to manage it without using dynamic queries (execute
'...') and code duplicating?

--
Regards,
Sergey Konoplev

On Nov 21, 2007, at 9:48 AM, Sergey Konoplev wrote:

> Hello,
>
> I have a procedure which takes few arguments. The arguments must
> affect expressions set of "order by" clause. For instance in one case
> ordering must be performed by 1st, 2nd (desc) and 3rd fields but in
> another by 3rd, 1st and 2nd fields.
>
> Is there a way to manage it without using dynamic queries (execute
> '...') and code duplicating?
>
> --
> Regards,
> Sergey Konoplev

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
either running a mostly duplicated query (the only difference in the
order  by) or each branch generating the order by expression as a
string which you then use in an EXECUTE statement.  If anyone knows
different, I'd love to hear it.

Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

> 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

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:

select col1, col2, col3 from mytable
 where ...
 order by case when $1 = '1' then col1 end,
              case when $1 = '1' then col2 else col3 end desc,
              case when $1 = '2' then col1 end,
              case when $1 = '2' then col2 end
;

This would work as following when $1 = '1':
select col1, col2, col3 from mytable
 where ...
 order by col1, col2desc, null, null;

and when $1 = '2':
select col1, col2, col3 from mytable
 where ...
 order by null, col3 desc, col1, col2