Question 197377 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – how to insert multiple commands into a prepared statement

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)
Tags:

2

Answers


  1. 0

    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;$$;
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search