Feature request? temp.table=TRUE

[GoogleCodeExporter]

Much of *my* use of RPostgreSQL is likely to follow the following pattern:

(1) Have a relatively small R data set for which I want to obtain matching data 
from PostgreSQL
(2) Send R data to PostgreSQL. Ask latter to match up and compile related data.
(3) Pull data back into R for further analysis.

It would be aesthetically pleasing if this left little trace in PostgreSQL. One 
option is to DROP TABLE at the end. An alternative would be to use CREATE TEMP 
TABLE. To do this with dbWriteTable, I changed two lines in 
PostgreSQLSupport.R. First, this one:

  postgresqlWriteTable <- function(con, name, value, field.types, row.names = TRUE,
    overwrite = FALSE, append = FALSE, temp.table=FALSE, ..., allow.keywords = FALSE) {

then, this one:

  sql0 <- if (temp.table) "create temp table " else "create table "
  sql1 <- paste(sql0, postgresqlQuoteId(name), "\n(\n\t", sep="")

I am not sure if this is a very useful (or desirable) feature (for example, can 
one eke performance improvements out of this?), but it seems to work, so I 
thought I'd share it.

Original issue reported on code.google.com by iand...@gmail.com on 27 Apr 2011 at 4:15

[GoogleCodeExporter]

Change to enhancement request.

Original comment by ne...@neiltiffin.com on 28 Apr 2011 at 3:25

• Added labels: Type-Enhancement
• Removed labels: Type-Defect

[GoogleCodeExporter]

I actually don't recommend to use temporary table as current rpostgresql does 
not guarantee that all db operations are sent through a single connection. See 
issue 8.  
So this feature requires suppression of the implicit duplication of the 
connection at first.

Original comment by tomoa...@kenroku.kanazawa-u.ac.jp on 25 Sep 2011 at 5:34

[GoogleCodeExporter]

Although issue 8 is fixed, temp table will not be visible with dbExistsTable().

For the purpose to ensure that little trace is left, it would be more robust to
use dbGetQuery(con, "BEGIN TRANSACTION")
and not committing the change or dbRollBack() at an appropriate time.

Another possibility is to use sqldf package. sqldf package allows sql 
manipulation
on data.frames without the need to considering the database behind. What you 
can do
might be a bit restricted than direct access. So, I am not very sure if this 
fits you,
but in general it seems sufficient for simple tasks.

Original comment by tomoa...@kenroku.kanazawa-u.ac.jp on 30 Mar 2012 at 4:16

[GoogleCodeExporter]

Thanks for the follow-up and the BEGIN TRANSACTION suggestion.

Thanks also for continued work on RPostgreSQL. I noticed a couple of days ago 
that the change in r219 (don't use make.db.names) broke some code I had relying 
on "row.names" becoming "row_names", but it was easy to fix and the new 
approach makes for nicer "round-tripping" of data from R to PostgreSQL and back 
to R.

Original comment by iand...@gmail.com on 2 Apr 2012 at 2:38