Обсуждение: use cursor in a function
This is a repost of a question which still confuses me. I wish to write a function that returns NUM_ROWS for a particular condition AND a subset of those rows (eg by using LIMIT and OFFSET). I imagine having function b that returns a reference to a cursor and NUMROWS. In function a I use the cursor to effectively do the LIMIT and OFFSET, and append the NUM_ROWS column to all of the results. Is this sensible, efficient, feasible? I have not used cursors before. Or should I simply do 2 separate functions, 1 for NUM_ROWS, the other for the result set I want? Help much appreciated! Rory -- Rory Campbell-Lange <rory@campbell-lange.net> <www.campbell-lange.net>
Rory Campbell-Lange wrote: > Is this sensible, efficient, feasible? I have not used cursors before. > > Or should I simply do 2 separate functions, 1 for NUM_ROWS, the > other for the result set I want? It's hard to offer advice (at least for me) without more context. What are you trying to accomplish (in more detail), and in what type of application (web, fat client, etc)? Joe
On 17/06/03, Joe Conway (mail@joeconway.com) wrote:
> Rory Campbell-Lange wrote:
> >Is this sensible, efficient, feasible? I have not used cursors before.
> >
> >Or should I simply do 2 separate functions, 1 for NUM_ROWS, the
> >other for the result set I want?
>
> It's hard to offer advice (at least for me) without more context. What
> are you trying to accomplish (in more detail), and in what type of
> application (web, fat client, etc)?
Its a web page <page> of <pages> scenario.
I need to find the total number of rows returned by a specific WHERE,
and turn that into <pages>.
I also need to return a set of rows, using LIMIT and OFFSET to grab a
subset of the rows and return these to the client.
I wondered if I could do this with one call rather than two. My complete
ignorance of how to use cursors is demonstrated in my second imaginary
example! I realise I can do more or less what I want in a single query
by using a LEFT OUTER JOIN to count(n_id) on a duplicate WHERE query.
Just wondered if cursors provide a cleaner looking query to do that.
Thanks!
Rory
Instead of going (psuedocode):
SELECT into record
n_id
FROM
table
WHERE
complex_where;
numrows := NUMROWS;
FOR resulter IN
numrows, this, that, tother
FROM
table
WHERE
complex_where
LIMIT
limit
OFFSET
offset
LOOP
return next resulter;
END LOOP;
I imagine going:
OPEN mycursor FOR
SELECT
this, that, tother
FROM
table
WHERE
complex_where;
numrows := mycursor->NUMROWS (!);
cursor_move(offset);
cursor_get(limit);
somehow get cursor values into a RECORD...
--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>
In a nutshell:
Is there a way of finding out how many rows (ROW_COUNT) are in a cursor
select? If one can, is there a way of returning a RECORD containing the
refcursor and the ROW_COUNT?
So far I've only been able to get a clue from:
1) Define function
create or replace function mycur(refcursor) returns refcursor AS '
BEGIN
open $1 for select * from abc;
RETURN $1;
END;
' LANGUAGE 'plpgsql';
2) Call function
temporary=> select mycur('cur');
mycur
-------
cur
(1 row)
3) Try to find end of cursor
temporary=> move 99 from cur;
MOVE 5
On 17/06/03, Joe Conway (mail@joeconway.com) wrote:
> Rory Campbell-Lange wrote:
> >Is this sensible, efficient, feasible? I have not used cursors before.
> >
> >Or should I simply do 2 separate functions, 1 for NUM_ROWS, the
> >other for the result set I want?
>
> It's hard to offer advice (at least for me) without more context. What
> are you trying to accomplish (in more detail), and in what type of
> application (web, fat client, etc)?
--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>
Rory Campbell-Lange wrote: > In a nutshell: > > Is there a way of finding out how many rows (ROW_COUNT) are in a cursor > select? If one can, is there a way of returning a RECORD containing the > refcursor and the ROW_COUNT? > You aren't buying anything by using a cursor. AFAICS the only way that a cursor would be a benefit, would be if it could persist from page to page. Then you could grab just the needed tuples without requerying. But I'm not aware of any way to do that. So you might as well run a single "SELECT count(*) ... WHERE your_criteria_here" to get the overall count once, and then run your LIMIT/OFFSET query directly for each page. Joe
On 17/06/03, Joe Conway (mail@joeconway.com) wrote:
> Rory Campbell-Lange wrote:
> >In a nutshell:
> >
> >Is there a way of finding out how many rows (ROW_COUNT) are in a cursor
> >select? If one can, is there a way of returning a RECORD containing the
> >refcursor and the ROW_COUNT?
> >
>
> You aren't buying anything by using a cursor. AFAICS the only way that a
> cursor would be a benefit, would be if it could persist from page to
> page. Then you could grab just the needed tuples without requerying. But
> I'm not aware of any way to do that.
>
> So you might as well run a single
> "SELECT count(*) ... WHERE your_criteria_here"
> to get the overall count once, and then run your LIMIT/OFFSET query
> directly for each page.
I understand. I was trying to avoid having to replicate a very long,
complex query twice.
I was hoping to be able to use mycurcal() to return the row count on the
cursor (which I hoped would record all the rows in ROW_COUNT), then do a
MOVE and then a FETCH to simulate OFFSET and LIMIT.
However I get the following:
temporary=> select * from mycurcal();
NOTICE: Row Count 1
mycurcal
----------
1
(1 row)
create or replace function mycur(refcursor) returns refcursor AS '
BEGIN
open $1 for select * from abc;
RETURN $1;
END;
' LANGUAGE 'plpgsql';
create or replace function mycurcal() returns integer AS '
DECLARE
rc INTEGER;
this record;
BEGIN
select mycur(''cur'') into this;
GET DIAGNOSTICS rc = ROW_COUNT;
RAISE NOTICE '' Row Count % '', rc;
--fetch all in cur;
RETURN 1;
END;
' LANGUAGE 'plpgsql';
--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>