This section contains the create and insert statements to run the examples from Chapter 7, “Partial Results” in a PostgreSQL database.

The test approach for the scalability of Top-N queries is the same as used in the “Testing and Scalability” chapter.

Querying Top-N Rows

First, using an index for the where clause only:

Postgresql Simple Cursor Example

Then, using a pipelined Top-N with an index covering the order by clause:

Postgresql Cursor Example Meaning

Michael Fuhr wrote: Right - when you open a cursor PostgreSQL doesn't know how many rows it will return. PostgreSQL selects a query plan based on an.estimate. of how many rows the query will return, but until you fetch all the rows you can't know for sure how many rows there will be. So if i make a but data set as result of a cursor I only 'pay' for the rows I actually fetch? You can call a PostgreSQL stored procedure and process a result set in a.NET application, for example, in C# application using Npgsql.NET data provider. Note that you do not need to know the name of the cursor to process the result set. // Start a transaction NpgsqlTransaction t = conn. The following diagram illustrates how to use a cursor in PostgreSQL: First, declare a cursor. Next, open the cursor. Then, fetch rows from the result set into a target. In this example, the SELECT statement of the cursor retrieves data from the products table. The FOR LOOP statement opened, fetched each row in the result set, displayed the product information, and closed the cursor. B) Cursor FOR LOOP with a SELECT statement example. The following example is equivalent to the example above but uses a query in a cursor FOR LOOP statement.

Paging Through Results

The following function uses both methods to fetch the result page-wise. The select statement in the end prepares the statistics on screen.

Postgresql Cursor Example Key

Window-Functions

Postgres Cursor Java Example

PostgreSQL supports window-Functions, but does, as of release 9.1, not use indexes for the best benefits.

Postgresql Refcursor

The database reads the entire table (Seq Scan) and sorts it (Sort). There is no Limit operation that would indicate awareness of the statements purpose.