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
>