title: How to use variable inside quoted string in postgres dynamic SQL
body: <p>I created the following function with plpgsql which takes 3 parameters.</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION public.most_service_calls(
      comp_id integer,
      calls integer,
      months integer)
      RETURNS TABLE(state character varying, city character varying, cust_name character varying, num_calls bigint, cost numeric) 
      LANGUAGE 'plpgsql'
  
      COST 100
      VOLATILE 
      ROWS 1000
  AS $BODY$
  
  Begin
  return query execute                   
  
  'select * from 
  (select l.state, l.city, l.cust_name, count(distinct a.svc_ord_nbr) num_calls, 
    round(avg(a.std_labr_net_amt_dcrncy) + avg(a.travel_net_amt_dcrncy), 2)
      from dmt_mas_svc_ord_fact a
      inner join dmt_mas_cust_dim b on a.shipto_cust_id = b.cust_id
      inner join store_location l on b.cust_name = l.cust_name
      inner join company co on b.cust_lvl_2_nbr = co.company_nbr
      where b.sap_sls_org_name like ''%Stanley US%''
         and a.create_dtm &gt;= now() - interval '' $3 months''
         and co.company_id = $1
      group by l.state, l.city, l.cust_name
      order by l.state, l.city, l.cust_name ) q
   where num_calls &gt;= $2'
   using comp_id, calls, months;
  
   end;
  
  $BODY$;
  </code></pre>
  
  <p>Since the query is quoted, all the single quoted strings are double quoted. Three variables are represented by <code>$1</code>, <code>$2</code>, <code>$3</code>. It is the variable inside a string that is causing the trouble. <code>a.create_dtm &gt;= now() - interval '' $3 months''</code></p>
  
  <p>When I run the function, it seems to ignore whatever third parameter I provided. Therefore, all the following return the same result.</p>
  
  <pre><code>select * from most_service_calls(1,5,1)
  select * from most_service_calls(1,5,12)
  select * from most_service_calls(1,5,24)
  </code></pre>
  
  <p>And it turned out, <code>$3</code> inside '' '' is taken as 3 since the result matches that of the query with <code>3 months</code> hardcoded in the query.</p>
  
  <p>What is the correct way to include the variable inside a string in a quoted query like this?</p>