Обсуждение: ORDER BY clause in aggregate doesn't work well with multi argument aggregates
ORDER BY clause in aggregate doesn't work well with multi argument aggregates
От
Pavel Stehule
Дата:
Hello It is maybe a bug. I cannot use ORDER BY clause in two parameter aggregate. create aggregate la(text, text) (SFUNC=listagg2_transfn, STYPE=internal, FINALFUNC=listagg_finalfn); postgres=# select la(town,',') from country; la -------------------------------Prague,Brno,Bratislava,Kosice (1 row) but when I add ORDER BY clause postgres=# select la(town order by town,',') from country; ERROR: function la(character varying) does not exist LINE 1: select la(town order by town,',') from country; ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. postgres=# Probably it don't use correct count of parameters, because when I add one parameter aggregate: postgres=# create aggregate la(text) (SFUNC=listagg1_transfn, STYPE=internal, FINALFUNC=listagg_finalfn); CREATE AGGREGATE postgres=# select la(town order by town,',') from country; la ----------------------------BratislavaBrnoKosicePrague (1 row) Then it working, but it call wrong aggregates. Regards Pavel Stehule
Pavel Stehule <pavel.stehule@gmail.com> writes: > It is maybe a bug. I cannot use ORDER BY clause in two parameter aggregate. I think you don't understand the syntax. Put the aggregate arguments first, then the ORDER BY. regards, tom lane
Re: ORDER BY clause in aggregate doesn't work well with multi argument aggregates
От
Pavel Stehule
Дата:
2009/12/25 Tom Lane <tgl@sss.pgh.pa.us>: > Pavel Stehule <pavel.stehule@gmail.com> writes: >> It is maybe a bug. I cannot use ORDER BY clause in two parameter aggregate. > > I think you don't understand the syntax. Put the aggregate arguments > first, then the ORDER BY. > Sorry, I don't see it. Please, could you be more descriptive. > regards, tom lane >
Re: ORDER BY clause in aggregate doesn't work well with multi argument aggregates
От
Pavel Stehule
Дата:
2009/12/25 Pavel Stehule <pavel.stehule@gmail.com>: > 2009/12/25 Tom Lane <tgl@sss.pgh.pa.us>: >> Pavel Stehule <pavel.stehule@gmail.com> writes: >>> It is maybe a bug. I cannot use ORDER BY clause in two parameter aggregate. >> >> I think you don't understand the syntax. Put the aggregate arguments >> first, then the ORDER BY. >> > > Sorry, I don't see it. Please, could you be more descriptive. > again sory, I see it. all is ok Thank You Pavel Stehule >> regards, tom lane >> >
Re: ORDER BY clause in aggregate doesn't work well with multi argument aggregates
От
Pavel Stehule
Дата:
2009/12/25 Tom Lane <tgl@sss.pgh.pa.us>: > Pavel Stehule <pavel.stehule@gmail.com> writes: >> It is maybe a bug. I cannot use ORDER BY clause in two parameter aggregate. > > I think you don't understand the syntax. Put the aggregate arguments > first, then the ORDER BY. > I was wrong. But it should be syntax error no? SELECT foo(expr ORDER BY expr, .....) currently it quietly ignore arguments over ORDER BY clause. Pavel > regards, tom lane >
Pavel Stehule <pavel.stehule@gmail.com> writes: > I was wrong. But it should be syntax error no? > SELECT foo(expr ORDER BY expr, .....) > currently it quietly ignore arguments over ORDER BY clause. No, because you could have more than one ORDER BY item. regards, tom lane
Re: ORDER BY clause in aggregate doesn't work well with multi argument aggregates
От
Pavel Stehule
Дата:
2009/12/25 Tom Lane <tgl@sss.pgh.pa.us>: > Pavel Stehule <pavel.stehule@gmail.com> writes: >> I was wrong. But it should be syntax error no? > >> SELECT foo(expr ORDER BY expr, .....) > >> currently it quietly ignore arguments over ORDER BY clause. > > No, because you could have more than one ORDER BY item. > I see it now. hmm - this isn't bullet-prof design :( ok, thank you Pavel > regards, tom lane >
Pavel Stehule <pavel.stehule@gmail.com> writes: > 2009/12/25 Tom Lane <tgl@sss.pgh.pa.us>: >> No, because you could have more than one ORDER BY item. > hmm - this isn't bullet-prof design :( I notice that ORDER BY 'x' draws an error at the query level but not within aggregates or window functions. I wonder if we should have the "SQL99" ORDER BY code throw an error for a simple literal ORDER BY item. There isn't any visible use for ordering by a constant, and this would catch some possible misunderstandings. For instance, it would have complained about Pavel's original example in this thread, and we could word the error message to make it clear that the system thinks that's an ORDER BY value not an aggregate argument. regards, tom lane