using window functions

Daniel Manesajian <manesaj@hotmail.com> writes:

When I try a simple query "select avg(my_int) over (order by my_int rows unbounded preceding) order by 1;" I get an error:
ERROR: syntax error at or near "over"

You sure you're actually talking to the 8.4 server? Because that's
exactly what you'd get if you tried to feed the command to an older
release.

You might in fact need a newer snapshot too. The main window functions
commit went in 2008-12-28 but I recall that there were some mop-up fixes
afterwards. At best a 1-Jan snapshot would be a bit unstable.

regards, tom lane

Daniel Manesajian, 17.03.2009 04:33:

Hi,

I'm trying to get an advance taste of the window function feature that I
believe is supposed to be in 8.4. I'm running 8.4devel snapshot (dated
Jan-01 which seems kind of old) grabbed from the snapshot page on the
postgresql website.

When I try a simple query "select avg(my_int) over (order by my_int rows
unbounded preceding) order by 1;" I get an error:

ERROR: syntax error at or near "over"

Do I have to build from source to try window functions?

I'm using the build from January 1st (on Windows, provided by EnterpriseDB) and your statement gives me an error at the keyword "unbounded". If I leave that out or add the missing range, it works.

Are you sure you are connecting to the correct installation?

Btw: I'm not sure whether your statement makes sense. As you are not using partion by or selecting other columns from the table, the same could be achieved without a window function. But I guess you just wanted to play around ;)

Thomas

Hi Tom,

psql --version returns 8.4devel. I would in fact like to try a newer snapshot, but I'm not sure how to get one. The devel snapshot 'one-click-installer' link provided at postgresql.org gives a 2009-01-01 version with no options for anything newer or older. I was thinking my next step would be to build from source by grabbing the next daily snapshot. Is this the thing to do?

Regards,

Daniel

To: manesaj@hotmail.com
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] using window functions
Date: Mon, 16 Mar 2009 23:58:25 -0400
From: tgl@sss.pgh.pa.us

Daniel Manesajian <manesaj@hotmail.com> writes:

When I try a simple query "select avg(my_int) over (order by my_int rows unbounded preceding) order by 1;" I get an error:
ERROR: syntax error at or near "over"

You sure you're actually talking to the 8.4 server? Because that's
exactly what you'd get if you tried to feed the command to an older
release.

You might in fact need a newer snapshot too. The main window functions
commit went in 2008-12-28 but I recall that there were some mop-up fixes
afterwards. At best a 1-Jan snapshot would be a bit unstable.

regards, tom lane

_________________________________________________________________
Windows Live™ Groups: Create an online spot for your favorite groups to meet.
http://windowslive.com/online/groups?ocid=TXT_TAGLM_WL_groups_032009