0

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

user2966197
  • 2,793
  • 10
  • 45
  • 77
  • I don't recall that SQL requires to put quotes around column names... I would assume that `tmp_cp <- paste(c(paste("copy (select"), paste(temp_list$column_name), "from table2)"), collapse = " ") ` should suffice – David Arenburg Jul 22 '14 at 22:24

2 Answers2

1

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')))
G. Grothendieck
  • 254,981
  • 17
  • 203
  • 341
  • I want each of col1, col2 ... inside separate `""` like `"col1"`,`"col2"` and so on – user2966197 Jul 22 '14 at 22:11
  • but now as per your output there will be extra \ with each column name – user2966197 Jul 22 '14 at 22:16
  • I am not saying about double quotes.The output which you have shown above contains an extra \ (escape charater) associated with each quote. – user2966197 Jul 22 '14 at 22:19
  • No. There are no extra backslashes. The backslashes you see are NOT part of the string. I have added a `cat` at the end to show it. – G. Grothendieck Jul 22 '14 at 22:21
  • when I am using `shQuote()` then I am getting single quote `'` instead of double quotes `"` around column names. I have updated my post above to depict my new statements under update section – user2966197 Jul 22 '14 at 23:28
  • I assume its system dependent (I'm using Windows). I have added a second argument to force it to produce double quotes. – G. Grothendieck Jul 22 '14 at 23:34
0

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)")
Hong Ooi
  • 56,353
  • 13
  • 134
  • 187