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