Thread: Find out the number of rows returned by refcursor?
What is the best way to find out the total number of rows returned by an refcursor? This would allow the client user to know the total amount of rows as they are using FETCH FORWARD/BACKWARD. For example let's say that an refcursor has 300 rows. The user fetches 20 at a time. I would like the user to know that there are 300 possible rows. regards, karen
Karen Hill wrote: > What is the best way to find out the total number of rows returned by > an refcursor? This would allow the client user to know the total > amount of rows as they are using FETCH FORWARD/BACKWARD. > > For example let's say that an refcursor has 300 rows. The user fetches > 20 at a time. I would like the user to know that there are 300 > possible rows. I probably should re-phrase that question. CREATE OR REPLACE FUNCTION foobar( refcursor ) RETURNS refcurser AS ' BEGIN OPEN $1 FOR SELECT * FROM t ORDER by z; END; ' LANGUAGE 'plpgsql'; BEGIN; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- Is there a way to know the total number of rows the cursor is capable of traversing without using --count? Perhaps GET DIAGNOSTICS ROW_COUNT? SELECT foobar('mycursor'); -- I want to avoid using count(*) for performance reasons. Getting the total number of rows the cursor --has. I suspect it there is a system variable that has this information...I just don't know which one it --is. SELECT COUNT(*) FROM t; COMMIT;
"Karen Hill" <karen_hill22@yahoo.com> writes: > -- Is there a way to know the total number of rows the cursor is > capable of traversing without using --count? If you want an accurate count, the only way is to traverse the cursor. Consider using MOVE FORWARD ALL and noting the rowcount, then MOVE BACKWARD ALL to reset the cursor (the latter at least should be reasonably cheap). If you can settle for a (potentially very inaccurate) estimate, consider using EXPLAIN on the query and noting the planner's rowcount estimate. regards, tom lane
Tom Lane wrote: > "Karen Hill" <karen_hill22@yahoo.com> writes: > > -- Is there a way to know the total number of rows the cursor is > > capable of traversing without using --count? > > If you want an accurate count, the only way is to traverse the cursor. > Consider using MOVE FORWARD ALL and noting the rowcount, then MOVE > BACKWARD ALL to reset the cursor (the latter at least should be > reasonably cheap). > Cool. Quick question, how does one go about noting the rowcount? Using the rowcount in get diagnostics or something else? regards, karen.
Karen Hill wrote: > Tom Lane wrote: > > "Karen Hill" <karen_hill22@yahoo.com> writes: > > > -- Is there a way to know the total number of rows the cursor is > > > capable of traversing without using --count? > > > > If you want an accurate count, the only way is to traverse the cursor. > > Consider using MOVE FORWARD ALL and noting the rowcount, then MOVE > > BACKWARD ALL to reset the cursor (the latter at least should be > > reasonably cheap). > > > > Cool. Quick question, how does one go about noting the rowcount? > Using the rowcount in get diagnostics or something else? > A "MOVE FORWARD ALL FROM cur;" statement returns "MOVE x". Where x is the number moved. The result seems to be of a NOTICE type, and I'm not sure how I can pass that as a result from a pgsql function. I guess what I'm looking for is this, if it is possible: CREATE OR REPLACE FUNCTION FOOBAR(refcursor , out refcursor , out total int4) AS ' BEGIN OPEN $1 FOR SELECT * FROM t_table ORDER by c_column DESC; total := (MOVE FORWARD ALL FROM $1); MOVE BACKWARD ALL FROM $1; $2 := $1; END; ' LANGUAGE plpgsql; Thanks in advance. Also, is this possible? I would like to be able to plug in the name of the refcursor returned by the above stored procedure and be able to fetch data: CREATE OR REPLACE FUNCTION MOVE(refcursor) RETURNS ROWTYPE AS ' BEGIN FETCH FORWARD 20 FROM $1; END; ' LANGUAGE plpgsql; regards, karen.
"Karen Hill" <karen_hill22@yahoo.com> writes: >> Cool. Quick question, how does one go about noting the rowcount? >> Using the rowcount in get diagnostics or something else? > A "MOVE FORWARD ALL FROM cur;" statement returns "MOVE x". Where x is > the number moved. The result seems to be of a NOTICE type, and I'm not > sure how I can pass that as a result from a pgsql function. Um ... sorry, you can't really. I was thinking of doing this from client-side logic, where that count is normally going to be accessible as part of the command status. plpgsql has a pretty limited set of cursor features --- I don't think it lets you get at MOVE ALL at all, let alone check the number of rows moved over. Can you push the operation over to the client side? (We ought to try to improve that situation in future releases, but that won't help you today.) regards, tom lane