function retuning refcursor, order by ignored?

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

novnov <novnovice@gmail.com> writes:

It seems that at least in the way I've written the function below, ORDER BY
is ignored.

Please provide a test case backing up that statement?

regards, tom lane

Hmm, well if both of you say that ORDER BY is not somehow ignored by
refcursor functions then I'm sure you're right. I'm just very clusmy in my
testing of the output...somehow the output order by is being lost. I've been
testing via the results in a web app and have had issues with executing the
proc in postgres directly. I think the example you've provided here may help
me.

Thanks to both of you.

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#a10867454
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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.

novnov escribi�:

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

Cursors are closed when transactions finish. Try issuing a BEGIN before
calling the function (and COMMIT after the fetch).

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Right...I see I'd left BEGIN; off after the first couple of trys.

Thanks

Alvaro Herrera-7 wrote:

novnov escribió:

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

Cursors are closed when transactions finish. Try issuing a BEGIN before
calling the function (and COMMIT after the fetch).

--
Alvaro Herrera
http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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

While a hard coded order by clause works; passing the order by as a param is
ignored as I've implemented below. The order by value is being passed as
expected (tested by outputing the value in a column). I've called like so:

ups=# begin;
BEGIN
ups=# select proc_item_list(1,true,'item_id');
proc_item_list
---------------------
<unnamed portal 12>
(1 row)

ups=# fetch all from "<unnamed portal 12>";

CREATE or REPLACE FUNCTION "public"."proc_item_list"(
IN "pint_org_id" int4,
IN "pbool_active" bool,
IN "pstr_orderby" varchar)
RETURNS "pg_catalog"."refcursor" AS
$BODY$
DECLARE
ref refcursor;
BEGIN
OPEN ref FOR SELECT item_id, item_name, item_org_id, item_active
FROM public.t_item
WHERE item_org_id = "pint_org_id" and item_active = "pbool_active"
ORDER BY "pstr_orderby";
RETURN ref;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

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

On Wed, May 30, 2007 at 11:40:15AM -0700, novnov wrote:

While a hard coded order by clause works; passing the order by as a param is
ignored as I've implemented below. The order by value is being passed as
expected (tested by outputing the value in a column). I've called like so:

What you're doing is equivalent to ORDER BY 'constant' which is totally
meaningless. If you want to control the column name dynamically, you
need to build the query dynamically, with EXECUTE for example.

ORDER BY "pstr_orderby";

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

novnov escribi�:

While a hard coded order by clause works; passing the order by as a param is
ignored as I've implemented below. The order by value is being passed as
expected (tested by outputing the value in a column).

It doesn't because the value is expanded as a constant, therefore all
rows have the same value and the sort is a no-op. Try using EXECUTE
(although I admit I don't know if you are able to do an OPEN CURSOR with
EXECUTE)

--
Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4
Bob [Floyd] used to say that he was planning to get a Ph.D. by the "green
stamp method," namely by saving envelopes addressed to him as 'Dr. Floyd'.
After collecting 500 such letters, he mused, a university somewhere in
Arizona would probably grant him a degree. (Don Knuth)

Yes, thanks, you're all very helpful and I completely appreciate it.

For future reference, here is the adapted procedure. I wonder if the way I'm
dealing with the boolean param (using the if then to set a stand in
variable) is as clean as it could be...but it does work.

CREATE or REPLACE FUNCTION "public"."proc_item_list"(
IN "pint_org_id" int4,
IN "pbool_active" bool,
IN "pstr_orderby" varchar)
RETURNS "pg_catalog"."refcursor" AS
$BODY$
DECLARE
ref refcursor;
strSQL varchar;
strActive varchar;
BEGIN
if "pbool_active" = true then
strActive = 'true';
else
strActive = 'false';
end if;

strSQL := 'SELECT item_id, item_name, item_org_id, item_active
FROM public.t_item
WHERE item_org_id = ' || "pint_org_id" || ' and item_active = ' ||
strActive ||
' ORDER BY ' || "pstr_orderby";
OPEN ref FOR EXECUTE strSQL;
RETURN ref;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

Alvaro Herrera-7 wrote:

novnov escribió:

While a hard coded order by clause works; passing the order by as a param
is
ignored as I've implemented below. The order by value is being passed as
expected (tested by outputing the value in a column).

It doesn't because the value is expanded as a constant, therefore all
rows have the same value and the sort is a no-op. Try using EXECUTE
(although I admit I don't know if you are able to do an OPEN CURSOR with
EXECUTE)

--
Alvaro Herrera
http://www.amazon.com/gp/registry/CTMLCN8V17R4
Bob [Floyd] used to say that he was planning to get a Ph.D. by the "green
stamp method," namely by saving envelopes addressed to him as 'Dr. Floyd'.
After collecting 500 such letters, he mused, a university somewhere in
Arizona would probably grant him a degree. (Don Knuth)

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

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