Thread: order by when using cursors

order by when using cursors

From
Patrick Scharrenberg
Date:
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



Re: order by when using cursors

From
"Pavel Stehule"
Date:
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
>


Re: order by when using cursors

From
"Pavel Stehule"
Date:
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
>>
>


Re: order by when using cursors

From
Patrick Scharrenberg
Date:
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