Re: using PREPAREd statements in CURSOR

28 jun 2007 kl. 16.45 skrev Tom Lane:

> =?ISO-8859-1?Q?Bj=F6rn_Lundin?= <b(dot)f(dot)lundin(at)gmail(dot)com> writes:
>> I'm connecting via libpq and want to
>> use prepared statements in a cursor.
>
> You can't.

That explains why I could not find an example...

> If you're just interested in fetching a large query result in
> sections,

No, I'm writing a sql-binding to libpq, for use with a current
system, that is written with Oracle as db.
The processes all use a sql-binding to Oracle,
and in order not to rewrite them, I want to keep the cursor handling.

The processes are filled with code snippets like this:

prepare(Statement1,"select col3,col4 from table_a where col1 = :COL_A
and col2 = :COL_B");
set(Statement1,"COLA_A",10);
set(Statement1,"COLA_B","ABC");
open_cursor(Statement1)
loop
fetch(Statement1, end_of_set);
exit when end_of_set;
get(Statement1,"col3",var3);
get(Statement1,"col4",var4);
end loop;
close_cursor(Statement);

--use var3 and var4 here

So I redesigned, and use plain strings, that I pass to
libpq. They are build on the fly.

Since I prefer keeping the cursor, over the prepared statements,
is there any performance gain I can do,
besides fetching say 100 rowa at a time, rather than 1.
(The fetch above is a wrapper to libpq's fetch)

I'm thinking, is it better to explicitly cast the bind variables in
the statement string?
The above statement would be sent to libpq as

declare cursor xyz as select col3,col4 from table_a where col1 = 10
and col2 = 'ABC'

Would it be better to send it as

declare cursor xyz as select col3,col4 from table_a where col1 =
10::integer and col2 = 'ABC::text'

I will use integer, float, character(n), date, time w/o tz
(should perhaps be 'ABC::character(3)')

Or should I use say int4 instead of integer?
The character(3) are constrained by the host language,
ie Ada.

> A well-thought-out API proposal
> would probably be favorably received.

Hmm, I would think that would be over my head...
But, in a way I'm glad that the PQPrepare is not an option,
passing variables in an array from Ada to C would
give at least some headache.

The 'set' approach would be easier, when interfacing from other
languages, I think.

> (yes, DECLARE CURSOR
> is planned differently than a plain select).

And which way is to be preferred?

/Björn