Postgresql for loop cursor how to#
Postgresql for loop cursor update#
Postgresql for loop cursor series#

Postgresql for loop cursor update#

I attributed this relative slowness to the fact I was using a FOR loop, but when I tested a simple update of 250K rows on PostgreSQL the simple update ran in the same time as a FOR loop doing the same thing. My corresponding PostgreSQL FOR loop handles the job too, but can only manage about a quarter of the throughput (compared to MS SQL) on the same desktop machine. In SQL server, a quirky update is perfect for this and it can process thousands of rows per second on my desktop server.

The batch of payments could potentially contain hundreds of thousands of rows for thousands of different accounts.

Postgresql for loop cursor series#

What I need to implement is an allocation of a large number of payments against a series of outstanding balances, depleting the remaining payments as they're applied, and reducing the outstanding balances. If I only needed running totals, I'd use your example. Using one of our get cursor functions we can now easily count the number of returned cursor records (only 1) by passing the function as argument.Yes - I totally agree. Get a reference cursorĬREATE FUNCTION get_refcursor(OUT return_cursor refcursor) We can simplify this by using an OUT argument instead allowing us to omit both the declaration block and return statement, see the Create Function documentation. OPEN return_cursor FOR SELECT 'One Record' We will probably want to use this with cursor producing functions, like: - Get a reference cursor

With 50,000 records I had to increase the step size to 1000 before I noticed any improvement over FETCH ALL so unless there is something else worth doing simultaneously the incremental approach is less optimal. GET DIAGNOSTICS _move_count := ROW_COUNT

Count the actual number of rows incremented To rectify this we can look up ROW_COUNT and increment by the actual amount moved instead. Increasing the step size does improve performance but the result will be rounded up because FOUND will report success if the cursor has moved at all. Increment the cursor position and count the rows Function returning the number of rows available in the cursorĬREATE FUNCTION get_cursor_size(_cursor refcursor)Īnother approach is to LOOP through the cursor until FOUND returns falsey, however this approach is notably slower than even the FETCH ALL method from the original example in the question. Modify the function to take a refcursor as argument and instead execute the MOVE cursor statement directly which then makes ROW_COUNT available. The holy grail of PostgreSQL cursor record count methods we've all been waiting for. NOTE: The following examples do not reset the cursor position back to 0 as with the original example, allowing the function to be used with all cursor types especially NO SCROLL cursors which will reject backward movement by raising an error. This is not clearly stipulated in the PostgreSQL documentanion per se, but considering that MOVE does not produce any actual results it might make sense enough to be excused. GOTCHA: Using EXECUTE does not update the GET DIAGNOSTICS for MOVE while it does for FETCH, and neither statements will update the FOUND variable. As it turns out we can indeed retrieve ROW_COUNT diagnostics from a MOVE cursor statement. Yes it is possible to use MOVE instead of FETCH to count the records in a cursor, with slightly improved performance. Here is a solution proposal, how do you think I can improve it ? (and is it possible to use MOVE instead of FETCH to retrieve the x value ?) - Function returning the number of rows available in the cursorĬREATE FUNCTION get_cursor_size(_cursor_name TEXT)ĮXECUTE format('FETCH FORWARD ALL FROM %I', _cursor_name) ĮXECUTE format('MOVE ABSOLUTE 0 FROM %I', _cursor_name) GET DIAGNOSTICS := ROW_COUNT only works for FETCH but not MOVE.

Postgresql for loop cursor how to#

The result is a command tag written to stdout, and I do not know how to retrieve that value in a pgsql function. In that case, a MOVE FORWARD ALL FROM statement returns MOVE x. Since the rows represented by a held cursor are copied into a temporary file or memory area, I am wondering if it is possible to retrieve that number in a straightforward way or if the only solution is to fetch all the records to count them. This form is used for PL/pgSQL functions that do not return a set. RETURN RETURN expression RETURN with an expression terminates the function and returns the value of expression to the caller. I would like to retrieve the number of rows that can be fetched by the cursor. There are two commands available that allow you to return data from a function: RETURN and RETURN NEXT. I have a cursor created using the WITH HOLD option that allows the cursor to be used for subsequent transactions.