Обсуждение: 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