Re: order by when using cursors

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
>>
>