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 not found THEN
exit;
ELSE
RETURN NEXT;
END IF;
END LOOP;
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(at)web(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>