Re: Getting ROW_COUNT from MOVE in 8.3

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!