Thread: Suggestion for parameterized queries

Hi,

Parameterized queries (PREPARE/EXECUTE), is a great thing, but it
would even be better if the DECLARE CURSOR could use a PREPAREd
statement, to declare a "server-side" cursor with a parameterized
query, to use the benefits of DECLAREd cursors (no fetching of all the
result set on the client, use the binary mode, use the scrollable
option or the FOR UPDATE option).

Something like:

    PREPARE s1 (type,..) AS SELECT ....
    DECLARE c1 (value,...) CURSOR FROM s1
    FETCH c1

or have a new OPEN instruction to pass parameters:

    PREPARE s1 (type,..) AS SELECT ....
    DECLARE c1 CURSOR FROM s1
    OPEN c1 USING (value,...)
    FETCH c1

That would be an incredible improvement to make PostgreSQL compete
with other databases like Informix, Oracle, Db2 UDB, SQL Server (I
wrote some drivers for these database servers).

For now we cannot use DECLARE CURSOR in our PostgreSQL driver because
of this limitation.... I could build the SQL statement with literal
values, but since you have now parametrized queries I would prefer to
use that...

Thank you!

Sebastien FLAESCH
Database driver writer at www.4js.com

Sebastien FLAESCH wrote:
> Hi,
>
> Parameterized queries (PREPARE/EXECUTE), is a great thing, but it would
> even be better if the DECLARE CURSOR could use a PREPAREd statement, to
> declare a "server-side" cursor with a parameterized query, to use the
> benefits of DECLAREd cursors (no fetching of all the result set on the
> client, use the binary mode, use the scrollable option or the FOR UPDATE
> option).

> For now we cannot use DECLARE CURSOR in our PostgreSQL driver because
> of this limitation.... I could build the SQL statement with literal
> values, but since you have now parametrized queries I would prefer to
> use that...

You can define a function that returns a cursor, if that is of any use
to you.

--
   Richard Huxton
   Archonet Ltd

Richard Huxton wrote:
> Sebastien FLAESCH wrote:
>
>> Hi,
>>
>> Parameterized queries (PREPARE/EXECUTE), is a great thing, but it
>> would even be better if the DECLARE CURSOR could use a PREPAREd
>> statement, to declare a "server-side" cursor with a parameterized
>> query, to use the benefits of DECLAREd cursors (no fetching of all the
>> result set on the client, use the binary mode, use the scrollable
>> option or the FOR UPDATE option).
>
>
>> For now we cannot use DECLARE CURSOR in our PostgreSQL driver because
>> of this limitation.... I could build the SQL statement with literal
>> values, but since you have now parametrized queries I would prefer to
>> use that...
>
>
> You can define a function that returns a cursor, if that is of any use
> to you.
>

Thanks for the idea, but actually we need that as a basic SQL feature,
because we write a database driver.

How to write a generic function that can execute any SELECT statement?

I can imagine that one param of the function would be the SQL text,
but what about passing a variable list of parameters?

Seb