![]() ![]() ![]() 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. 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. I would like to retrieve the number of rows that can be fetched by the cursor. I switched from %I to %s, because the regclass parameter is automatically properly escaped when (automatically) converted to text.I have a cursor created using the WITH HOLD option that allows the cursor to be used for subsequent transactions. or go with the more elegant approach of using a regclass type:ĬREATE OR REPLACE FUNCTION f_nocurs(_tbl regclass)ĮXECUTE format('UPDATE %s SET tbl_id = tbl_id + 1000', _tbl).either pass and escape them separately,.Answer for (ii)Ī schema-qualified table name like trace.myname actually consists of two identifiers. Or better, yet (if possible!): Rethink your problem in terms of set-based operations and execute a single (dynamic) SQL command: - Set-base dynamic SQLĬREATE OR REPLACE FUNCTION f_nocurs(_tbl text)ĮXECUTE format('UPDATE %I SET tbl_id = tbl_id + 1000', _tbl) Use the implicit cursor of a FOR loop instead: CREATE OR REPLACE FUNCTION f_curs2(_tbl text)įOR _ctid IN EXECUTE 'SELECT ctid FROM ' || quote_ident(_tbl) FOR UPDATEĮXECUTE format('UPDATE %I SET tbl_id = tbl_id + 100 WHERE ctid = $1', _tbl) There is normally no need for explicit cursors in plpgsql. There is also a variant of the FOR statement to loop through cursors, but it only works for bound cursors. OPEN _curs FOR EXECUTE 'SELECT * FROM ' || quote_ident(_tbl) FOR UPDATE ĮXECUTE format('UPDATE %I SET tbl_id = tbl_id + 10 WHERE ctid = $1', _tbl) CREATE OR REPLACE FUNCTION f_curs1(_tbl text) Note that ctid is only guaranteed to be stable within the same transaction. I use the system column ctid instead to determine the row without knowing the name of a unique column. Hence, you cannot use the special syntax WHERE CURRENT OF cursor. Cursors are not visible inside the command. Explicit (unbound) cursorĮXECUTE is not a "clause", but a PL/pgSQL command to execute SQL strings. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |