Getting ROW_COUNT from MOVE in 8.3

On sön, 2010-10-24 at 15:41 +0200, Reuven M. Lerner wrote:

But is there any way for me to get, in 8.3, the
number of rows over which a cursor has skipped? Keep in mind that
after
this count has executed, we're then going to rewind the cursor,
chunking
through the result set with a separate function.

You could store the query result in a temporary table.

Hi, Peter. You wrote:

But is there any way for me to get, in 8.3, the
number of rows over which a cursor has skipped? Keep in mind that
after
this count has executed, we're then going to rewind the cursor,
chunking
through the result set with a separate function.

You could store the query result in a temporary table.

Yeah, that's what we're doing now. But (a) I'm hoping that move will be
faster, because we're not copying data, but just moving through it, and
(b) I wanted to benchmark the different options. But I'm glad to know
that this isn't a bad way to go, given the lack of information.

That said, I'm sort of surprised that the psql client is able to show us
the number of rows through which MOVE operated, while we're unable to
access that number in pl/pgsql. But hey, that's yet another welcome
addition to 9.0...

Reuven

--
Reuven M. Lerner -- Web development, consulting, and training
Mobile: +972-54-496-8405 * US phone: 847-230-9795
Skype/AIM: reuvenlerner

On 24 Oct 2010, at 15:41, Reuven M. Lerner wrote:

I've managed to improve things quite a bit by using cursors, but I've been stumped in trying to find a replacement for the COUNT(*). I wrote a function that works great on 9.0:

CREATE OR REPLACE FUNCTION count_the_rows() RETURNS INTEGER AS $$
DECLARE
mycursor CURSOR FOR SELECT * FROM test_table;
number_of_rows integer := 0;
BEGIN
OPEN mycursor;
MOVE ALL IN mycursor;
GET DIAGNOSTICS number_of_rows := ROW_COUNT;
RETURN number_of_rows;
END;
$$ LANGUAGE 'plpgsql';

Unfortunately, the project is using 8.3, and the function refuses to even compile, due to the "MOVE ALL". When we change it to something else (such as MOVE 20000000, which returns the actual number of rows skipped to psql), or ABSOLUTE -1, we get 0 back from ROW_COUNT.

I recall movable cursors weren't supported in pl/pgsql until 8.4.

You can use (movable) cursors in SQL though. That probably requires some client-side code, as things like GET DIAGNOSTICS aren't available in sql functions, but it may be of use to you. I've done this in a PHP web-application a couple of times.

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.

!DSPAM:737,4cc531ef10289398819850!

Please leave the ML in the reply-list, so that others might benefit from the answers, comment on the solution, etc etc. ;)

On 25 Oct 2010, at 13:55, AI Rumman wrote:

I am using Postgresql 8.1 and facing the similar problem.
Can you provide the link where I get the information what you did with PHP?

Not really, as I wrote it myself at a company that no longer exists; There is no link.

You can however look at the documentation, specifically:
http://www.postgresql.org/docs/8.1/interactive/sql-declare.html and
http://www.postgresql.org/docs/8.1/interactive/sql-fetch.html

You can retrieve the number of rows after MOVE FORWARD ALL (similar to the below posting) either from pg_num_rows() or by reading out the return value (not the result set!) of the query.
I don't remember exactly how I did it, I used this method several years ago and have since dropped PHP as a programming language I support.

On Mon, Oct 25, 2010 at 1:29 PM, Alban Hertroys <dalroi@solfertje.student.utwente.nl> wrote:
On 24 Oct 2010, at 15:41, Reuven M. Lerner wrote:

I've managed to improve things quite a bit by using cursors, but I've been stumped in trying to find a replacement for the COUNT(*). I wrote a function that works great on 9.0:

CREATE OR REPLACE FUNCTION count_the_rows() RETURNS INTEGER AS $$
DECLARE
mycursor CURSOR FOR SELECT * FROM test_table;
number_of_rows integer := 0;
BEGIN
OPEN mycursor;
MOVE ALL IN mycursor;
GET DIAGNOSTICS number_of_rows := ROW_COUNT;
RETURN number_of_rows;
END;
$$ LANGUAGE 'plpgsql';

Unfortunately, the project is using 8.3, and the function refuses to even compile, due to the "MOVE ALL". When we change it to something else (such as MOVE 20000000, which returns the actual number of rows skipped to psql), or ABSOLUTE -1, we get 0 back from ROW_COUNT.

I recall movable cursors weren't supported in pl/pgsql until 8.4.

You can use (movable) cursors in SQL though. That probably requires some client-side code, as things like GET DIAGNOSTICS aren't available in sql functions, but it may be of use to you. I've done this in a PHP web-application a couple of times.

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.

!DSPAM:737,4cc5778810291816532262!