[R-sig-DB] dbWriteTable on virtual linux box

[Lee Hachadoorian]

On 02/28/2011 07:20 PM, Tomoaki NISHIYAMA wrote:
> Hi,
>
>> However, at a different
>> location, I only have access to a Windows computer, so I am running R on
>> Ubuntu on VirtualBox. On this setup, when I try the above code, I get:
>
>
> What version of RPostgreSQL are you using?
>
> Is the PostgreSQL server working on the same virtualbox or are
> you to communicate the PostgreSQL server running on the host
> environment?
>
> If you are using an old version of RPostgreSQL, it cannot
> dbWriteTable to other hosts, but the most
> recent release version (0.1-7) should work.
>
> Another comment on your code:
>> dbWriteTable(conn, "<schema_name>.<table_anme", df1, row.names = FALSE)
>
>
> the table argument will only treated as the table name even if the string
> contains one or more periods in current implementation.
> If you want to write in non-default schema you need to change the
> default schema beforehand.
>
> For future implementation, I am thinking of allowing
> something like c("schemaname", "tablename"),
> but not sure on how many people agree, and is not implemented anyway.
Tomoaki,

You were right, I was using version 0.1-6. I upgraded to 0.1-7, and now
it, but with the additional complication, as you describe, that table
names are quoted. I can't figure out how to write to the desired schema.
In SQL I would use

SET search_path = myschema;

But when I

dbSendQuery(conn, "SET search_path = myschema;")
dbGetQuery(conn, "SHOW search_path")

     search_path
1 "$user",public

because the search_path only applies to the current session, and the
session ends when dbSendQuery returns a result.

On the other hand

dbGetQuery(connLocalhost, "SET search_path = myschema; SHOW search_path;")

  search_path
1        myschema

but since dbWriteTable takes a table name (which it quotes) rather than
a SQL statement, this approach can't be followed. So is there a way to
write to anything other than the public schema in version 0.1-7?

Thanks,
--Lee

-- 
Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center