Position within an open cursor using a query in PostgreSQL

I have a cursor declared on a query and want to re-pos开发者_运维技巧ition within that open cursor using another query on the same table, e.g.

Here is my cursor;

DECLARE mycursor CURSOR FOR SELECT * FROM mytable order by somedate;

This is the position I want to get to: select ROWNUMBER() from mytable where name = "fred"

Using ROWNUMBER() (or some other construct) I want to position in my open cursor.

I know I can use Fetch/Move to position within my cursor but the positioning is not absolute.

Can this be done?

Unless I'm reading your question wrong, the best you can do is this:

DECLARE mycursor CURSOR FOR
SELECT *, rank() OVER (ORDER BY somedate) FROM mytable ORDER BY somedate;

If you know the row number in advance you can jump straight to it with move/fetch; and if you don't but want to know it, you can access it using as you fetch.

That said, note that the query itself will be slower. So you'll be better off with a limit/offet if you already know the position of your row, or counting the rows as you go from your app if you don't.