CODEWITHSUNDEEP
rpostgresqlrpostgresql
user2966197
user2966197

Reputation: 2991

generating sql statements from data returned in R by Postgresql

Fist I am executing following R commands which returns me a set of records from postgresql

col_qry <- paste("select column_name from table1",sep="")
rs_col <- dbSendQuery(r,col_qry)
temp_list <- fetch(rs_col,n=-1)

The Data returned is displayed is following format when printed in R using print(temp_list)

          column_name
1         col1
2         col2
3         col3
4         col4

Now based on this returned data I want to generate another sql statement which should be like this

copy (select "col1","col2","col3","col4" from table2 )

When I do this

tmp_cp <- paste("copy (select ",col_list,",","from table2",sep="")

and print this tmp_cp then instead of one copy statement bunch of copy statements are printed, one for each column name inside select like this

copy (select col1 from table2 )
copy (select col2 from table2 )
copy (select col3 from table2 )
copy (select col4 from table2 )
and so on...

I want only one copy statement with all column names mentioned together, each quoted with "" and separated by ,. How can I do that?

UPDATE: When I am using these statement

col_list <- toString(shQuote(temp_list$column_name))

tmp_cp <- paste("copy (select ",col_list,",","from table2",sep="")

then only one statement is generated but the column names are inside single quote instead of double quotes like this : 

copy (select 'col1','col2','col3','col4' from table2 )

NOTE: I have mentioned 4 columns above but it is not that there are 4 columns only.columns can be many.For sake of explanation i have show 4 columns

Upvotes: 0

Views: 207

Answers (2)

Hong Ooi
Hong Ooi

Reputation: 57696

Nested paste with the collapse argument:

paste("copy (select", paste(cols, collapse=", "), "from table2)")

If you want quoted column names:

paste("copy (select", paste(shQuote(cols, "cmd"), collapse=", "), "from table2)")

Upvotes: 0

G. Grothendieck
G. Grothendieck

Reputation: 270298

Try this:

library(gsubfn)
sql <- fn$identity(
  "select `toString(shQuote(temp_list$column_name, 'cmd'))` from table2"
)

giving:

> sql
[1] "select \"col1\", \"col2\", \"col3\", \"col4\" from table2"

> cat(sql, "\n")
select "col1", "col2", "col3", "col4" from table2

This would work too and does not require any packages:

sprintf("select %s from table2", 
   toString(shQuote(temp_list$column_name, 'cmd')))

Upvotes: 1

Related Questions