Thread: retun cursor
HI, Can anybody please let me know how I can return cursors using plpgsql in postgresql. I'm moving my database from oracle to postgresql. I have lot of reports in Oracle that have to be converted to postgreSql. But I'm returning cursors in all the functions. I could not find any such option in postgresql. So please let me know how I can return record sets/cursors in postgresql functions.
On Sat, 30 Mar 2002, Madhavi Daroor wrote: > Can anybody please let me know how I can return cursors using plpgsql in > postgresql. > I'm moving my database from oracle to postgresql. I have lot of reports in > Oracle that have to be converted to postgreSql. But I'm returning cursors in > all the functions. I could not find any such option in postgresql. So please > let me know how I can return record sets/cursors in postgresql functions. I believe you can return open cursors from plpgsql via the refcursor type as of 7.2. I believe there's some documentation in the 7.2 plpgsql documentation of how to open the cursors and such.
On Thu, 6 Apr 2000, Madhavi Daroor wrote: > Hi, > I have tried the method you have suggested. And everytime I try to access > the function in which the refcursor is kept open, the only output I get is > "UNNAMED CURSOR" instead of the selected fields in the cursor. Right. You get a cursor name that you can fetch from, if you're in a transaction, after you select from the function, say it returns "unnamed cursor 1", IIRC you can do, fetch 10 from "unnamed cursor 1"; ...
On Mon, 8 Apr 2002, Madhavi Daroor wrote:
> When I do fetch 10 from "unnamed cursor 1"
> It says Query Executed Ok. But when am I really going to see the output?
> When Will I see the records? pLease explain the process.....WHat do I fetch
> the cursor into?
Here's a simple function and a fetch passed in through
psql -e
create table test1(a int);
CREATE
insert into test1 values (1);
INSERT 156460 1
insert into test1 values (2);
INSERT 156461 1
insert into test1 values (3);
INSERT 156462 1
insert into test1 values (4);
INSERT 156463 1
insert into test1 values (5);
INSERT 156464 1
create function testfunc1(int4) returns refcursor as '
declare
r refcursor;
begin
open r for select * from test1 where a>=$1;
return r;
end;'
language 'plpgsql';
CREATE
begin;
BEGIN
select testfunc1(2);
testfunc1
--------------------
<unnamed cursor 1>
(1 row)
fetch 1 from "<unnamed cursor 1>";
a
---
2
(1 row)
fetch all from "<unnamed cursor 1>";
a
---
3
4
5
(3 rows)
end;
COMMIT
Stephan Szabo wrote:
>
> On Thu, 6 Apr 2000, Madhavi Daroor wrote:
>
> > Hi,
> > I have tried the method you have suggested. And everytime I try to access
> > the function in which the refcursor is kept open, the only output I get is
> > "UNNAMED CURSOR" instead of the selected fields in the cursor.
>
> Right. You get a cursor name that you can fetch from, if you're in a
> transaction, after you select from the function, say it returns
> "unnamed cursor 1", IIRC you can do,
>
> fetch 10 from "unnamed cursor 1";
Should be "<unnamed cursor 1>" if memory serves.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
On Sat, 8 Apr 2000, Madhavi Daroor wrote: > Hi, > I followed the steps that you told me. An it worked fine. But I have one > problem and that is......when I try to fetch all the resords of a table, it > gives me the folowing error > > psqldb=# fetch all from "<unnamed cursor 4>"; > less: not found The pager for doing multiple page output isn't found. You should be able to turn it off with \pset pager I believe or you could check your PAGER environment variable. > And please explain to me how I could execute this in java. I don't know much about the java interface, but I'd guess it's pretty much the same as sending any other queries.
Hi,
This is what I got when I executed the statements outside the
BEGIN......END block
psqldb=# select f();
f
--------------------
<unnamed cursor 2>
(1 row)
psqldb=# fetch all from "<unnamed cursor 2>";
NOTICE: PerformPortalFetch: portal "<unnamed cursor 2>" not found
FETCH
What does the above statement mean? How do I call the function in Java? Is
there no way i could directly call the function in java.
Thanx,
Madhavi Daroor
-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
Sent: Monday, April 08, 2002 11:01 AM
To: Madhavi Daroor
Cc: pgsql-general@PostgreSQL.org
Subject: RE: [GENERAL] retun cursor
On Mon, 8 Apr 2002, Madhavi Daroor wrote:
> When I do fetch 10 from "unnamed cursor 1"
> It says Query Executed Ok. But when am I really going to see the output?
> When Will I see the records? pLease explain the process.....WHat do I
fetch
> the cursor into?
Here's a simple function and a fetch passed in through
psql -e
create table test1(a int);
CREATE
insert into test1 values (1);
INSERT 156460 1
insert into test1 values (2);
INSERT 156461 1
insert into test1 values (3);
INSERT 156462 1
insert into test1 values (4);
INSERT 156463 1
insert into test1 values (5);
INSERT 156464 1
create function testfunc1(int4) returns refcursor as '
declare
r refcursor;
begin
open r for select * from test1 where a>=$1;
return r;
end;'
language 'plpgsql';
CREATE
begin;
BEGIN
select testfunc1(2);
testfunc1
--------------------
<unnamed cursor 1>
(1 row)
fetch 1 from "<unnamed cursor 1>";
a
---
2
(1 row)
fetch all from "<unnamed cursor 1>";
a
---
3
4
5
(3 rows)
end;
COMMIT
Hi,
I followed the steps that you told me. An it worked fine. But I have one
problem and that is......when I try to fetch all the resords of a table, it
gives me the folowing error
psqldb=# fetch all from "<unnamed cursor 4>";
less: not found
Why does this happen? and after I get this error, if I try to fetch fewer
number or records say 5, then it returns 0 records.
Does this happen because there are large number of records in the cursor?
And please explain to me how I could execute this in java.
Thanx,
Madhavi Daroor
-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
Sent: Monday, April 08, 2002 11:01 AM
To: Madhavi Daroor
Cc: pgsql-general@PostgreSQL.org
Subject: RE: [GENERAL] retun cursor
On Mon, 8 Apr 2002, Madhavi Daroor wrote:
> When I do fetch 10 from "unnamed cursor 1"
> It says Query Executed Ok. But when am I really going to see the output?
> When Will I see the records? pLease explain the process.....WHat do I
fetch
> the cursor into?
Here's a simple function and a fetch passed in through
psql -e
create table test1(a int);
CREATE
insert into test1 values (1);
INSERT 156460 1
insert into test1 values (2);
INSERT 156461 1
insert into test1 values (3);
INSERT 156462 1
insert into test1 values (4);
INSERT 156463 1
insert into test1 values (5);
INSERT 156464 1
create function testfunc1(int4) returns refcursor as '
declare
r refcursor;
begin
open r for select * from test1 where a>=$1;
return r;
end;'
language 'plpgsql';
CREATE
begin;
BEGIN
select testfunc1(2);
testfunc1
--------------------
<unnamed cursor 1>
(1 row)
fetch 1 from "<unnamed cursor 1>";
a
---
2
(1 row)
fetch all from "<unnamed cursor 1>";
a
---
3
4
5
(3 rows)
end;
COMMIT
On Sat, 2000-04-08 at 17:26, Madhavi Daroor wrote: > Hi, > I followed the steps that you told me. An it worked fine. But I have one > problem and that is......when I try to fetch all the resords of a table, it > gives me the folowing error > > psqldb=# fetch all from "<unnamed cursor 4>"; > less: not found > > Why does this happen? and after I get this error, if I try to fetch fewer > number or records say 5, then it returns 0 records. less is a pager program. Either install it or set PAGER=more in your environment before running psql. > Does this happen because there are large number of records in the cursor? No; it is because your pager program is missing. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "And they questioned Him, saying "...Is it lawful for us to pay taxes to Caesar, or not? ...And He said to them "...render to Caesar the things that are Caesar's, and to God the things that are God's." Luke 20:21,22,25