Re: RPostgreSQL: prepared statements?

[NISHIYAMA Tomoaki]

Dear Pavel,

Thank you for the interest in contributing RPostgreSQL.
prepared statement is the second item of the TODO list
https://code.google.com/p/rpostgresql/source/browse/trunk/RPostgreSQL/inst/TODO

a patch is generally welcome.
However, I have a few notes.

1. currently I had difficulty in getting accepted in CRAN. The reason was
related to the different output in different platform/versions. The different
output of the test makes difficult for the CRAN maintainers to judge the
validity. I understand that they are so busy and have not a good solution
to perform a good test and still get into the CRAN.

2. While the prepared statement is generally good to have, it may not be
so good for the performance. Especially for the use case you have shown,
it could be faster to upload the probeids as a temporary table and
perform a single dbGetQuery rather than submitting multiple requests packed
in a function.

3. As a API, having to send the SQL statement at the time of querying
is not something expected for prepared statement. The shown example could
be rather called as dbGetQuery with vectorized parameters. We could use
the same name or a bit different name, but dbGetPreparedQuery doesn't fit.

See the document of PQprepare, PQexecPrepared, and PQexecParams
at
http://www.postgresql.org/docs/9.4/static/libpq-exec.html
--
Tomoaki NISHIYAMA

Advanced Science Research Center,
Kanazawa University,
13-1 Takara-machi,
Kanazawa, 920-0934, Japan


On 2015/06/09, at 7:34, Pavel Reich wrote:

> Dear Tomoaki,
>
> Thanks for the great and useful RPostgreSQL package, but are there any
> plans to support prepared statements/bound variables in queries? I
> checked https://code.google.com/p/rpostgresql/issues/list and found no
> tasks for it.
> I'd expect something like
>
> query <- "SELECT probe_type,next_base,color_channel FROM probes WHERE probeid=?"
> probe.types.df <- dbGetPreparedQuery(con,que,bind.data=data.frame(probeids=ids))
>
> available in RSQLite. Is it worth me investigating how to port it to
> RPostgreSQL in order to submit a patch?
>
> Thanks.
>
> Regards,
> Pavel.