Skip site navigation (1) Skip section navigation (2)

Function debugging question

From: "Daniel T(dot) Staal" <DStaal(at)usa(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: Function debugging question
Date: 2005-06-09 18:56:47
Message-ID: 54370.63.172.115.138.1118343408.squirrel@MageHandbook.com (view raw or flat)
Thread:
Lists: pgsql-novice
I have a function I'm working on that I'm trying to get working, and I
can't make out what the error message means, so I thought I'd run it
through here.

Here's the function:

CREATE OR REPLACE FUNCTION
	msgweekfilter(ibox char(20), part char(20), year int, week int, start
int, num int)
	RETURNS SETOF msgweek AS '
DECLARE
	r msgweek%ROWTYPE;
	curs1 refcursor;
	i integer;
BEGIN
	OPEN curs1 FOR SELECT * FROM  msgweek
		WHERE ibx = ibox AND (sibx = part OR ribx = part)
		AND eyear = year AND eweek = week
		ORDER BY count;

	FETCH ABSOLUTE start - 1 FROM cursl INTO r;

	FOR r IN FETCH FORWARD num FROM cursl LOOP
		RETURN NEXT r;
		i = i + 1;
		FETCH NEXT cursl INTO r;
	END LOOP;
	CLOSE cursl;
RETURN;

END' LANGUAGE 'plpgsql'

Yes, I know there are easier ways to do this.  (A simple LIMIT/OFFSET
would do the same.)  However, I need to get this function to work.  If I
can, I'd even like to change the 'FETCH FORWARD' into standard SQL. 
(Which I think would require a counted loop, aborting either if the count
is exceded or the invalid.  But that's the next question.)

The error I'm getting is:
ERROR:  syntax error at or near "ABSOLUTE" at character 344
LINE 13: FETCH ABSOLUTE start FROM cursl INTO r;
               ^

As far as I can tell, that line has the correct syntax, which leads me to
one of two conclusions: a) I don't know the correct syntax for FETCH, or
b) I missed something earlier.

Any ideas?

Daniel T. Staal

------------------------------------------------------------------------
This email copyright the author.  Unless otherwise noted, you are
expressly allowed to retransmit, quote, or otherwise use the contents
for non-commercial purposes.  This copyright will expire 5 years after
the author's death, or in 30 years, whichever is longer, unless such a
period is in excess of local copyright law.
------------------------------------------------------------------------

Responses

pgsql-novice by date

Next:From: Ennio-SrDate: 2005-06-09 19:39:46
Subject: How not to display system tables?
Previous:From: Oliver ElphickDate: 2005-06-09 17:51:10
Subject: Re: breaking out of a loop in plpgsql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group