Re: function retuning refcursor, order by ignored? - Mailing list pgsql-general

From novnov
Subject Re: function retuning refcursor, order by ignored?
Date
Msg-id 10876641.post@talk.nabble.com
Whole thread Raw
In response to Re: function retuning refcursor, order by ignored?  (Michael Fuhr <mike@fuhr.org>)
Responses Re: function retuning refcursor, order by ignored?
List pgsql-general
I have been able to run a test like you have in a query (if that's the right
term), and ORDER BY does work. When trying to run via psql, it fails, I
can't see what I'm doing wrong. (windows xp, postgres 8.2.0-1)

ups=# select proc_item_list(1,true);
   proc_item_list
--------------------
 <unnamed portal 3>
(1 row)

ups=# fetch all from "<unnamed portal 3>";
ERROR:  cursor "<unnamed portal 3>" does not exist
ups=#


Michael Fuhr wrote:
>
> On Tue, May 29, 2007 at 05:18:42PM -0700, novnov wrote:
>> It seems that at least in the way I've written the function below, ORDER
>> BY
>> is ignored.
>
> Works here (see below).  Can you post a complete example that shows
> ORDER BY being ignored?  Do you get different results from the
> cursor than if you execute the same query directly?  What version
> of PostgreSQL are you running?
>
>
> CREATE TABLE t_item (
>     item_id      integer PRIMARY KEY,
>     item_name    text NOT NULL,
>     item_org_id  integer NOT NULL,
>     item_active  boolean NOT NULL
> );
>
> INSERT INTO t_item VALUES (4, 'four', 1, true);
> INSERT INTO t_item VALUES (2, 'two', 1, true);
> INSERT INTO t_item VALUES (1, 'one', 1, true);
> INSERT INTO t_item VALUES (3, 'three', 1, true);
>
> BEGIN;
>
> SELECT proc_item_list(1, true);
>    proc_item_list
> --------------------
>  <unnamed portal 1>
> (1 row)
>
> FETCH ALL FROM "<unnamed portal 1>";
>  item_id | item_name | item_org_id | item_active
> ---------+-----------+-------------+-------------
>        1 | one       |           1 | t
>        2 | two       |           1 | t
>        3 | three     |           1 | t
>        4 | four      |           1 | t
> (4 rows)
>
> COMMIT;
>
> --
> Michael Fuhr
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>
>

--
View this message in context:
http://www.nabble.com/function-retuning-refcursor%2C-order-by-ignored--tf3837519.html#a10876641
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


pgsql-general by date:

Previous
From: Andrew Sullivan
Date:
Subject: Re: 8.3
Next
From: Ron Johnson
Date:
Subject: Re: problem calling psql multiple times from a script ?