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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-novice by date

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