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.
------------------------------------------------------------------------
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 |