如何在Prepared Statement中执行多条命令？解决多命令插入报错问题

这个错误的核心原因是：dbGetQuery（R的DBI包函数）默认使用预处理语句执行SQL，这类语句不允许在同一个查询字符串中包含多个独立的SQL命令（你的代码里同时放了SET search_path和SELECT两个命令）。

有两种简单的解决办法：

方法1：拆分SQL命令，单独执行SET语句 #

把设置schema路径的操作单独用dbExecute执行，再执行查询语句：

# 单独设置schema路径
dbExecute(prod, "set search_path to care;")

# 只保留查询语句
q1="
    SELECT COALESCE(c.date,a.date,jq.date,jn.date) reportmonth,registration,emailverified,application,submitted,eligible,jqtest,selected,joined
    from      ( SELECT date_trunc('month', createdon)::date as date, count(1) as registration,count(case when emailverified='Yes'then 1 end) as emailverified from contact group by 1)
    full join ( SELECT date_trunc('month', timecreated)::date as date, count(1) as joined from scholars where deleted='FALSE' group by 1 ) jn on jn.date=c.date
    order by 1;"

funnel=dbGetQuery(prod,q1)

方法2：在表名前指定schema前缀 #

不需要设置search_path，直接在查询的表名前加上schema名称（care.）：

q1="
    SELECT COALESCE(c.date,a.date,jq.date,jn.date) reportmonth,registration,emailverified,application,submitted,eligible,jqtest,selected,joined
    from      ( SELECT date_trunc('month', createdon)::date as date, count(1) as registration,count(case when emailverified='Yes'then 1 end) as emailverified from care.contact group by 1)
    full join ( SELECT date_trunc('month', timecreated)::date as date, count(1) as joined from care.scholars where deleted='FALSE' group by 1 ) jn on jn.date=c.date
    order by 1;"

funnel=dbGetQuery(prod,q1)

内容的提问来源于stack exchange，提问作者Nazima