Re: order by when using cursors

2008/6/18 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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 not found THEN
exit;
ELSE
RETURN NEXT;
END IF;
END LOOP;
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(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
>>
>