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

>                        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



>>                        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


>                        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


>                        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