By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,246 Members | 1,480 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,246 IT Pros & Developers. It's quick & easy.

MOVE doesn't return number of rows

P: n/a
Dear Postgresql experts,

According to the documentation for MOVE, it returns the number of rows
that it has moved over. It seems to me that this is true for MOVE
FORWARD n, but not for MOVE RELATIVE n or MOVE ABSOLUTE n when it always
returns 1:

db=> declare c scroll cursor for select * from p;
DECLARE CURSOR
db=> move absolute -1 in c;
MOVE 1
db=> move absolute 1 in c;
MOVE 1
db=> move absolute 10 in c;
MOVE 1
db=> move relative 10 in c;
MOVE 1
db=> move relative 100 in c;
MOVE 1
db=> move forward 100 in c;
MOVE 100

Is this a bug?

What I'm actually trying to do is to find the number of rows in the
query result. I was hoping to be able to do a "MOVE ABSOLUTE -1" to get
this. Any other suggestions?

Taking a step further back, here's the real problem: I want to present
the user with an index page to chunks of a table, e.g.

Aardvark - Frog
Frozen - Rabbit
Rabies - Zoo

So I need to get something like rows 1, n/3-1, n/3, 2n/3-1, 2n/3, n.
Efficiency is of concern. Is there some way of asking "WHERE row_number
MOD x < 2"? Currently I am running a count(*) version of the query to
get the number of rows and then running it again with a cursor and
fetching the necessary rows. Any other ideas?

This is with 7.4.2.

Regards,

--Phil.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Phil Endecott <sp**************************@chezphil.org> writes:
According to the documentation for MOVE, it returns the number of rows
that it has moved over.
That's a documentation bug. CVS tip has the correct explanation:

The count is the number of rows that a FETCH command with the same
parameters would have returned (possibly zero).
What I'm actually trying to do is to find the number of rows in the
query result. I was hoping to be able to do a "MOVE ABSOLUTE -1" to get
this. Any other suggestions?


MOVE FORWARD ALL.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.