Thread: using window functions

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?
 
Thank you,
 
Daniel

---

Windows Live™: Keep your life in sync. Check it out.

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
thetable, 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. Check it out.