Обсуждение: function retuning refcursor, order by ignored?
It seems that at least in the way I've written the function below, ORDER BY
is ignored. I've seen hints that one can declare the refcursor as a specific
query and that apparently the order by clause there is respected. But I
don't find much by way of examples in the docs or on this list. I will
eventually need LIMIT and OFFSET as well as ORDER BY. It would be extremely
helpful if someone could take the function below and rearrange so that it
supports ORDER BY, LIMIT and OFFSET.
CREATE or REPLACE FUNCTION "public"."proc_item_list"(
IN "pint_org_id" int4,
IN "pbool_active" bool)
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 item_id;
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#a10865322
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
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/ > From each according to his ability. To each according to his ability to litigate.
Вложения
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.