Re: Getting ROW_COUNT from MOVE in 8.3

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(at)solfertje(dot)student(dot)utwente(dot)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(at)postgresql(dot)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!