“Asensitive”, which means that the sensitivity is implementation dependent, is the same as “insensitive” in PostgreSQL. PostgreSQL cursors are always insensitive, which means that changes in the underlying data after the cursor has started processing are not visible in the data fetched from the cursor. PostgreSQL does not implement sensitive cursors: that would be difficult, because a statement always sees a stable snapshot of the data in PostgreSQL. A sensitive cursor reflects modifications of the underlying data one consequence of this is that scrolling back to a previous row might fetch a different result. The SQL standard distinguishes SENSITIVE, INSENSITIVE and ASENSITIVE cursors. * this cursor would be implicitly scrollable */įOR SELECT * FROM generate_series(1, 10) Here is a little example that showcases scrollable cursors: Such cursors incur an overhead, because the server must cache the entire result set. Other, more complicated execution plans require the explicit keyword SCROLL for the cursor to become scrollable. PostgreSQL calculates query result rows “on demand” and streams them to the client, so scrollable cursors for such queries come with no extra cost. A cursor for a query with such an execution plan is implicitly scrollable, that is, you can move the cursor position backwards in the result set. Some execution plans, like a B-tree index scan or a sequential scan, can be executed in both directions. SQL cursors are closed with the CLOSE statement, or by the end of the transaction. Like PL/pgSQL, SQL also has a MOVE statement that moves the cursor position without retrieving rows. There is also an SQL statement FETCH that is more powerful than its PL/pgSQL equivalent, in that it can fetch more than one row at a time. ASENSITIVE and INSENSITIVE are redundant in PostgreSQL and are there for SQL standard compatibility.WITH HOLD creates a cursor that is not automatically closed at the end of a transaction.SCROLL means that you can move the cursor position backwards to fetch the same rows several times.BINARY will fetch the results in the internal binary format, which may be useful if you want to read bytea columns and avoid the overhead of escaping them as strings.Here is a short description of the different options: You create a cursor with the DECLARE statement:ĭECLARE name SCROLL ]ĬURSOR FOR query Cursors in SQLĪ special feature of PostgreSQL is that you can use cursors in SQL. In the above example we had no problem, because a DO statement is always executed in a single transaction anyway.Ĭursors are automatically closed at the end of a transaction, so it is usually not necessary to explicitly close them, unless they are part of a long-running transaction and you want to free the resources allocated by the statement. That is not surprising, since a cursor is a single SQL statement, and an SQL statement is always part of one transaction. One basic property of a PostgreSQL cursor is that it only exists for the duration of a database transaction. However, we have to use a cursor in our case, since we need to execute a dynamic SQL statement inside the loop. Such a join is more efficient, because it does all the work in a single statement. Note that it is often possible to avoid a cursor loop by using a join in the database. END LOOP ”, which uses a cursor “under the hood”), but I wanted to make the cursor explicit. The above is not the most readable way to write this in PL/pgSQL (you could have used “ FOR v_schema, v_name IN SELECT. In this example, the SELECT is executed concurrently with the DROP TABLE statements. * system variable FOUND is set by FETCH */ Another advantage is that a cursor allows you to have more than one SQL statement running at the same time, which is normally not possible in a single database session.Ī simple example for PL/pgSQL code that uses a cursor would be: Cursors are particularly useful in procedural code on the client or in the database, because they allow you to loop through the query results. A cursor marks a position within a result set. In contrast, a cursor allows you to fetch the result rows one by one. During normal query execution, you receive the whole result set in one step. When a query is ready for execution, PostgreSQL creates a portal from which the result rows can be fetched. We will also see the dangers involved and how to properly use WITH HOLD cursors in a PL/pgSQL procedure. This article describes how cursors and transactions interact and how WITH HOLD can work around their limitations. Cursor plpgsql postgresql procedure transactionīoth cursors and transactions are basic ingredients for developing a database application.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |