Re: order by when using cursors

Hello

it's known problem - column and variable names collision, so when you
use any SQL statement inside procedure you have to be carefully about
using variable names.

postgres=# CREATE OR REPLACE FUNCTION testcur( OUT _a integer, OUT _b integer )
RETURNS SETOF RECORD AS $$
DECLARE      cur refcursor;
BEGIN      OPEN cur FOR SELECT * FROM ta ORDER BY a DESC;      LOOP              FETCH cur INTO _a, _b;              IF
notfound THEN                      exit;              ELSE                      RETURN NEXT;              END IF;
ENDLOOP;      CLOSE cur;
 
END;
$$ LANGUAGE 'PLPGSQL' ;


postgres=# select *from testcur();_a | _b
----+---- 4 |  3 3 |  1 2 |  4 1 |  2
(4 rows)

postgres=#

Regards
Pavel Stehule


2008/6/18 Patrick Scharrenberg <pittipatti@web.de>:
> Hi!
>
> I  did some experiments with cursors and found that my data doesn't get
> sorted by the "order by"-statement.
>
> Here is what I did:
>
> ----------------
>
> CREATE TABLE ta (
>  a integer NOT NULL,
>  b integer NOT NULL
> );
>
> insert into ta values(3,1);
> insert into ta values(1,2);
> insert into ta values(4,3);
> insert into ta values(2,4);
>
> CREATE OR REPLACE FUNCTION testcur( OUT a integer, OUT b integer )
> RETURNS SETOF RECORD AS $$
> DECLARE
>        cur refcursor;
> BEGIN
>        OPEN cur FOR SELECT * FROM ta ORDER BY a DESC;
>        LOOP
>                FETCH cur INTO a,b;
>                IF not found THEN
>                        exit;
>                ELSE
>                        RETURN NEXT;
>                END IF;
>        END LOOP;
>        CLOSE cur;
> END;
> $$ LANGUAGE 'PLPGSQL' ;
>
> SELECT * FROM testcur();
>
> ----------------
>
> As the result I get:
>
> 3       1
> 1       2
> 4       3
> 2       4
>
>
> Which is not ordered by column a!?
>
> Is this intended?
> Am I doing something wrong?
>
> I'm using Postgresql 8.3.1
>
> Patrick
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>