Re: are cursors necessary?

mh(at)pixar(dot)com (Mark Harrison) writes:

> In the program testlibpq.c, these five SQL statements are executed:
>
> res = PQexec(conn, "BEGIN");
> res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_database");
> res = PQexec(conn, "FETCH ALL in myportal");
> res = PQexec(conn, "CLOSE myportal");
> res = PQexec(conn, "END");
>
> Is this just to illustrate how to create transactions and cursors, or is there
> some material difference between trimming the program down to just:
>
> res = PQexec(conn, "select * from pg_database");
>
> Is there any value in my own query-only programs to declaring the cursor
> for each search?

Yes, there is value in it, albeit not likely for that particular
query.

Let's suppose that you have a query that is ultimately going to return 45 million records.

If you use "select * from gory_query", then they'll all show up at
once.

If you declare a cursor, you can, in a loop, do something like:

while (data_left()) do {
res = PQexec(conn, "fetch 1000 in gory_query_cursor");
do_stuff_with(res);
}

That'll pull the records in more bite-sized chunks, and allow you to
start processing data as soon as _some_ results come back. You don't
have to wait for the whole barrel of data to get dropped on you.
--
let name="cbbrowne" and tld="libertyrms.info" in String.concat "@" [name;tld];;
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 646 3304 x124 (land)