Move cursor
hi
i need to use the MOVE function on a cursor, for extracting special rows.
the function is written in pl/pgSQL
CREATE OR REPLACE FUNCTION TEST()
RETURNS void AS $$
DECLARE
childCursor CURSOR FOR select * from ...
childRecord adoc.xdb_child%ROWTYPE;
dep int4 :=3;
BEGIN
OPEN childCursor;
LOOP
MOVE FORWARD dep IN childCursor;
FETCH childCursor INTO childRecord;
EXIT WHEN not found;
raise notice '---> : % | % | %', childRecord.child_id,
childRecord.evolution, childRecord.indx;
END LOOP;
CLOSE childCursor;
RETURN void;
END;
$$ LANGUAGE plpgsql;
the function doesn't generate any error, but when i try to execute it, i get
this message :
ERROR: syntax error at or near "$1" at character 15
QUERY: MOVE FORWARD $1 IN $2
CONTEXT: PL/pgSQL function "test" line 10 at SQL statement
thanks in advance
Will
On Tue, Mar 08, 2005 at 04:23:37PM +0100, FERREIRA William (COFRAMI) wrote:
i need to use the MOVE function on a cursor, for extracting special rows.
the function is written in pl/pgSQL
I don't think PL/pgSQL implements cursors fully. In particular,
I don't see MOVE in the PL/pgSQL parser; I don't know if that's
intentional or an oversight. Maybe one of the developers will
comment.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
Michael Fuhr <mike@fuhr.org> writes:
I don't think PL/pgSQL implements cursors fully.
Its cursor facility is certainly far weaker than what's presently in the
main SQL language. I think this is at least partly historical accident
(ie we upgraded the main language and forgot about plpgsql).
regards, tom lane
On Tue, Mar 08, 2005 at 12:16:28PM -0500, Tom Lane wrote:
Michael Fuhr <mike@fuhr.org> writes:
I don't think PL/pgSQL implements cursors fully.
Its cursor facility is certainly far weaker than what's presently in the
main SQL language. I think this is at least partly historical accident
(ie we upgraded the main language and forgot about plpgsql).
Would adding MOVE to PL/pgSQL be a fairly trivial effort? If so
then I'd consider having a go at it myself, unless it's something
somebody else could bang out in five minutes (and was willing to
do so).
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
does i hope that CURSOR will be fully implemented soon ?
(or maybe you know an alternative for my problem ? :) )
-----Message d'origine-----
De : Michael Fuhr [mailto:mike@fuhr.org]
Envoyé : mardi 8 mars 2005 18:29
À : Tom Lane
Cc : FERREIRA William (COFRAMI); pgsql-general@postgresql.org
Objet : Re: [GENERAL] Move cursor
On Tue, Mar 08, 2005 at 12:16:28PM -0500, Tom Lane wrote:
Michael Fuhr <mike@fuhr.org> writes:
I don't think PL/pgSQL implements cursors fully.
Its cursor facility is certainly far weaker than what's presently in the
main SQL language. I think this is at least partly historical accident
(ie we upgraded the main language and forgot about plpgsql).
Would adding MOVE to PL/pgSQL be a fairly trivial effort? If so
then I'd consider having a go at it myself, unless it's something
somebody else could bang out in five minutes (and was willing to
do so).
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
This mail has originated outside your organization,
either from an external partner or the Global Internet.
Keep this in mind if you answer this message.
Import Notes
Resolved by subject fallback
Added to TODO:
o Add MOVE to PL/pgSQL
---------------------------------------------------------------------------
FERREIRA William (COFRAMI) wrote:
does i hope that CURSOR will be fully implemented soon ?
(or maybe you know an alternative for my problem ? :) )-----Message d'origine-----
De : Michael Fuhr [mailto:mike@fuhr.org]
Envoy? : mardi 8 mars 2005 18:29
? : Tom Lane
Cc : FERREIRA William (COFRAMI); pgsql-general@postgresql.org
Objet : Re: [GENERAL] Move cursorOn Tue, Mar 08, 2005 at 12:16:28PM -0500, Tom Lane wrote:
Michael Fuhr <mike@fuhr.org> writes:
I don't think PL/pgSQL implements cursors fully.
Its cursor facility is certainly far weaker than what's presently in the
main SQL language. I think this is at least partly historical accident
(ie we upgraded the main language and forgot about plpgsql).Would adding MOVE to PL/pgSQL be a fairly trivial effort? If so
then I'd consider having a go at it myself, unless it's something
somebody else could bang out in five minutes (and was willing to
do so).--
Michael Fuhr
http://www.fuhr.org/~mfuhr/This mail has originated outside your organization,
either from an external partner or the Global Internet.
Keep this in mind if you answer this message.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073