Обсуждение: order by when using cursors
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 $$ DECLAREcur refcursor; BEGINOPEN 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
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 >
2008/6/18 Pavel Stehule <pavel.stehule@gmail.com>: > 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' ; > one note: when you unlike prefixes in result, you can use in ORDER BY expression ordinal number of an output column, in this case 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 1 DESC; LOOP FETCH cur INTO a, b; IF notfound THEN exit; ELSE RETURN NEXT; END IF; ENDLOOP; CLOSE cur;END;$$ LANGUAGE 'PLPGSQL' ; other solution is using qualified names everywhere: CREATE OR REPLACE FUNCTION testcur( OUT a integer, OUT b integer ) RETURNS SETOF RECORD AS $$ DECLARE cur refcursor; BEGIN OPEN cur FOR SELECT ta.a, ta.b FROM ta ORDER BY ta.a DESC; -- ta.a qualified name LOOP FETCH cur INTO a, b; IF not found THEN exit; ELSE RETURN NEXT; END IF; END LOOP; CLOSE cur;END;$$ LANGUAGE'PLPGSQL' ; Pavel > > 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 >> >
Pavel Stehule wrote: >> 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. Oh, I didn't took notice of that. Now knowing it is not a bug and how it works, it makes things much easier!: Thank you! Patrick