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 > <dal...@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! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
