Cast of aggregate's type works:
# select avg(s)::int4 from foo;

but that doesn't work for with new windowing functions interface:
# select avg(s)::int4 OVER () from foo;
ERROR: syntax error at or near "OVER"
LINE 1: select avg(s)::int4 OVER () from foo;

Is that intentional?

--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/

Search Discussions

  • Greg Stark at Apr 14, 2009 at 1:25 pm

    2009/4/14 Teodor Sigaev <teodor@sigaev.ru>:
    select avg(s)::int4 OVER () from foo;
    You can put the cast outside the window expression such as:

    postgres=# select s,(avg(s) OVER (range between unbounded preceding
    and current row))::int4 from foo;
    s | avg
    ---+-----
    1 | 2
    2 | 2
    3 | 2
    (3 rows)


    However, I'm kind of confused by that result. Why does the range
    "between unbounded preceding and current row" seem to be doing the
    average of the whole result set? This is not related to the cast:

    postgres=# select s,avg(s) OVER (range between unbounded preceding and
    current row) from foo;
    s | avg
    ---+--------------------
    1 | 2.0000000000000000
    2 | 2.0000000000000000
    3 | 2.0000000000000000
    (3 rows)


    I haven't recompiled recently and I do recall some bug fixes a while
    back. Was this that? I'm recompiling now.






    --
    greg
  • Tom Lane at Apr 14, 2009 at 1:58 pm

    Greg Stark writes:
    However, I'm kind of confused by that result. Why does the range
    "between unbounded preceding and current row" seem to be doing the
    average of the whole result set?
    That's what it's supposed to do. "Current row" really includes all peers
    of the current row in the window frame ordering, and since you didn't
    specify any ORDER BY clause, all the rows are peers. If you put in
    "order by s" you'll get the result you were expecting:

    regression=# select s,(avg(s) OVER (range between unbounded preceding
    and current row)) from foo;
    s | avg
    ---+--------------------
    1 | 2.5000000000000000
    2 | 2.5000000000000000
    3 | 2.5000000000000000
    4 | 2.5000000000000000
    (4 rows)

    regression=# select s,(avg(s) OVER (order by s range between unbounded preceding

    and current row)) from foo;
    s | avg
    ---+------------------------
    1 | 1.00000000000000000000
    2 | 1.5000000000000000
    3 | 2.0000000000000000
    4 | 2.5000000000000000
    (4 rows)


    I suppose the SQL committee defined it like that to try to reduce the
    implementation dependency of the results.

    regards, tom lane
  • Tom Lane at Apr 14, 2009 at 1:35 pm

    Teodor Sigaev writes:
    Cast of aggregate's type works:
    # select avg(s)::int4 from foo;
    but that doesn't work for with new windowing functions interface:
    # select avg(s)::int4 OVER () from foo;
    ERROR: syntax error at or near "OVER"
    LINE 1: select avg(s)::int4 OVER () from foo;
    Is that intentional?
    You would need to put the cast around the whole "foo() over ()"
    construct. That's not a divisible unit.

    regards, tom lane

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedApr 14, '09 at 1:12p
activeApr 14, '09 at 1:58p
posts4
users3
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase