Skip site navigation (1) Skip section navigation (2)

Re: order by when using cursors

From: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
To: "Patrick Scharrenberg" <pittipatti(at)web(dot)de>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: order by when using cursors
Date: 2008-06-18 06:44:08
Message-ID: 162867790806172344i5d0e6a8t4175452b7773e097@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
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' ;


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
>

In response to

Responses

pgsql-sql by date

Next:From: Pavel StehuleDate: 2008-06-18 06:54:31
Subject: Re: order by when using cursors
Previous:From: Patrick ScharrenbergDate: 2008-06-18 06:02:43
Subject: order by when using cursors

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group