ORDER BY clause in aggregate doesn't work well with multi argument aggregates
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
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.
Show quoted text
regards, tom lane
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
Show quoted text
regards, tom lane
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
Show quoted text
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
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
Show quoted text
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