how to insert multiple commands into a prepared statement

Question

The problem is that putting queries together, I got this "error: cannot insert multiple commands into a prepared statement". What can I do?

q1="
    set search_path to care;
    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)

Answer 1

Try omitting the 'set search_path to talentcare;' part from your dbGetQuery. dbGetQuery only works with SELECT (or SELECT-like) queries. You can move the 'set search path' to a seperate dbExecute() statement.

Answer 2

You cannot do that.

In your case, a simple solution would be to get rid of the SET statement and run a single SELECT, where you qualify all tables with the schema name.

For other cases, an alternative is to create a PL/pgSQL function with the statements in it:

CREATE FUNCTION mystmt() RETURNS TABLE (date, ...)
LANGUAGE plpgsql
SET search_path = talentcare AS
$$BEGIN
   RETURN SELECT ...;
END;$$;