generating sql statements from data returned in R by Postgresql

Question

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

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
– David Arenburg, Commented Jul 22, 2014 at 22:24

G. Grothendieck · Accepted Answer · 2014-07-22 23:33:38Z

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')))

edited Jul 22, 2014 at 23:33

answered Jul 22, 2014 at 22:08

G. Grothendieck

273k18 gold badges220 silver badges365 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user2966197 Over a year ago

I want each of col1, col2 ... inside separate "" like "col1","col2" and so on

user2966197 Over a year ago

but now as per your output there will be extra \ with each column name

user2966197 Over a year ago

I am not saying about double quotes.The output which you have shown above contains an extra \ (escape charater) associated with each quote.

G. Grothendieck Over a year ago

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.

user2966197 Over a year ago

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

|

Hong Ooi · Accepted Answer · 2014-07-23 00:51:32Z

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)")

answered Jul 23, 2014 at 0:51

Hong Ooi

57.9k13 gold badges145 silver badges193 bronze badges

Collectives™ on Stack Overflow

generating sql statements from data returned in R by Postgresql

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related