Re: libpq and prepared statements progress for 8.0

> Anyone working on the libpq interface to prepared statements?
> We could really use that for DBD::Pg. Alternatively, if someone
> knows a trick to prepare an INSERT statement without knowing
> the data types, that would be neat too. For example:
>
> CREATE TABLE foobar(a INTEGER);
>
> These don't work:
>
> PREPARE st(text) AS INSERT INTO foobar(a) VALUES ($1);
>
> PREPARE st(unknown) AS INSERT INTO foobar(a) VALUES ($1);
>
> PREPARE st(unknown) AS INSERT INTO foobar(a) VALUES ($1::unknown);

Using PQExecParams is completely out of the question? How are you
executing your statements...PQExec?

Merlin

> > PREPARE st(unknown) AS INSERT INTO foobar(a) VALUES ($1);
>
> Using PQExecParams is completely out of the question? How are you
> executing your statements...PQExec?

A bit of context here. The perl DBD::Pg developers are trying to figure out
how to implement prepared queries sanely. As it stands now they're basically
writing off both binary prepared queries and SQL based prepared queries as
basically useless. It would be a shame to have a brand new binary protocol but
find it ignored by driver writers.

The problem is that you want to be able to do

$sth = $dbh->prepare("SELECT col_a FROM tab WHERE col_b = ?");
$sth->execute(1);
...

And not have to jump through hoops binding parameters to types and so on.

Ideally the database should treat the placeholder exactly as it would a
single-quoted constant. Ie, it should treat it as "unknown" and use the
context around it to determine what type reader function to use to read it.

That would mean the semantics would be exactly equivalent to:

SELECT col_a FROM tab WHERE col_b = '1';

Without this capability using prepared statements is simply too cumbersome for
application programmers. And in any case DBD::Pg wants to maintain backwards
compatibility with the existing drivers which don't require binding parameter
types because they simply interpolate the parameters into the query string.

--
greg

Greg Stark <gsstark(at)mit(dot)edu> writes:
> A bit of context here. The perl DBD::Pg developers are trying to figure out
> how to implement prepared queries sanely. As it stands now they're basically
> writing off both binary prepared queries and SQL based prepared queries as
> basically useless.

Really?

It would be cool if they'd stand up and identify themselves and mention
their concerns to the people who are doing the protocol and libpq work.
I sure haven't ever heard word one from anyone working on DBD::Pg.

I'll restrain myself from any stronger comments...

regards, tom lane