[R-sig-DB] How to write to database schema other than public using RPostgreSQL

[Xiaobo Gu]

My idea is to add a postgresTableQuoteName function as

postgresqlTableQuoteName <- function(identifier){
	names <- strsplit(identifier, ".", fixed=TRUE)[[1]]
	if (length(names) == 2){
		res <- paste(postgresqlQuoteId(names[1]),".",
postgresqlQuoteId(names[2]),sep="");
	}else{
		res <- postgresqlQuoteId(identifier);
	}
	return res;
}

then change the following lines of code in the postgresqlWriteTable as
1.
	change
        sql1 <- paste("create table ", postgresqlQuoteId(name),
"\n(\n\t", sep="")
        to
        sql1 <- paste("create table ", postgresqlTableQuoteName(name),
"\n(\n\t", sep="")
2.

change
	sql4 <- paste("COPY", postgresqlQuoteId(name), "FROM STDIN")
 to

	sql4 <- paste("COPY", postgresqlTableQuoteName(name), "FROM STDIN")

On Sun, Mar 6, 2011 at 12:18 PM, Xiaobo Gu <guxiaobo1982 using gmail.com> wrote:
> On Sun, Mar 6, 2011 at 9:03 AM, Tomoaki NISHIYAMA
> <tomoakin using kenroku.kanazawa-u.ac.jp> wrote:
>> Hi,
>>
>> Why don't you try
>>
>> dbGetQuery(con, "set search_path to amber")
>>
>> dbWriteTable(con, "rtest", df, row.names=FALSE)
>>
>> ?
> It does not work, dbExistsTable returns TRUE if tablename exists in
> any of the schemas and if tablename is not with the format
> schema.tablename;
>
>> dbExistsTable(con, "rtest")
> [1] TRUE
>> dbExistsTable(con, "public.rtest")
> [1] TRUE
>> dbExistsTable(con, "amber.rtest")
> [1] FALSE
>>
> So the idea is to let dbWriteTable work as dbExistsTable, if tablename
> is the with format schema.talename then the table will be created in
> the target schema, else it will be created in the default search path
> schema.
My idea is to add a postgresTableQuoteName function as

postgresqlTableQuoteName <- function(identifier){
	names <- strsplit(identifier, ".", fixed=TRUE)[[1]]
	if (length(names) == 2){
		res <- paste(postgresqlQuoteId(names[1]),".",
postgresqlQuoteId(names[2]),sep="");
	}else{
		res <- postgresqlQuoteId(identifier);
	}
	return res;
}

then change the following lines of code in the postgresqlWriteTable as
1.
	change
        sql1 <- paste("create table ", postgresqlQuoteId(name),
"\n(\n\t", sep="")
        to
        sql1 <- paste("create table ", postgresqlTableQuoteName(name),
"\n(\n\t", sep="")
2.

change
	sql4 <- paste("COPY", postgresqlQuoteId(name), "FROM STDIN")
 to

	sql4 <- paste("COPY", postgresqlTableQuoteName(name), "FROM STDIN")





>
>
>
>
>
>
>
>>
>> If table rtest exists in public you have to exclude public from
>> the search path. Isn't it?
>>
>>
>>
>> (2011/03/05 22:56), Xiaobo Gu wrote:
>>>
>>> On Sat, Mar 5, 2011 at 9:16 PM, Tomoaki NISHIYAMA
>>> <tomoakin using kenroku.kanazawa-u.ac.jp>  wrote:
>>>>
>>>> Hi,
>>>>
>>>> Did you use dbSendQeury() to change the search path or dbGetQeury() ?
>>>
>>> I have tried both, both failed, the last error is because there is
>>> already table named rtest.
>>>
>>>> dbGetQuery(con, "set search_path to amber,public;")
>>>
>>> NULL
>>>>
>>>> dbGetQuery(con, "show search_path;")
>>>
>>>     search_path
>>> 1 amber, public
>>>>
>>>> dbWriteTable(con, "rtest", df, row.names=FALSE)
>>>
>>> [1] FALSE
>>> Warning message:
>>> In postgresqlWriteTable(conn, name, value, ...) :
>>>   table rtest exists in database: aborting assignTable
>>>
>>>
>>>> As explained in the mail on 1 March on [R-sig-DB], dbSendQuery just sends
>>>> the query but does not finish the query
>>>> and other connection is created for later queries, which results as if
>>>> the
>>>> effect is not taken place.
>>>>
>>>> https://stat.ethz.ch/pipermail/r-sig-db/2011q1/001032.html
>>>>
>>>>
>>>
>>
>>
>