Postgres Pro
Downloads
Home   >   mailing lists

Thread: Move cursor

Move cursor

From
"FERREIRA William (COFRAMI)"
Date:
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

Re: Move cursor

From
Michael Fuhr
Date:
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/

Re: Move cursor

From
Tom Lane
Date:
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

Re: Move cursor

From
Michael Fuhr
Date:
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/

Re: Move cursor

From
"FERREIRA William (COFRAMI)"
Date:

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.

Re: Move cursor

From
Bruce Momjian
Date:
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 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.

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