How to use variable inside quoted string in postgres dynamic SQL

Question

I created the following function with plpgsql which takes 3 parameters.

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 >= 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 >= $2'
 using comp_id, calls, months;

 end;

$BODY$;

Since the query is quoted, all the single quoted strings are double quoted. Three variables are represented by $1, $2, $3. It is the variable inside a string that is causing the trouble. a.create_dtm >= now() - interval '' $3 months''

When I run the function, it seems to ignore whatever third parameter I provided. Therefore, all the following return the same result.

select * from most_service_calls(1,5,1)
select * from most_service_calls(1,5,12)
select * from most_service_calls(1,5,24)

And it turned out, $3 inside '' '' is taken as 3 since the result matches that of the query with 3 months hardcoded in the query.

What is the correct way to include the variable inside a string in a quoted query like this?

harmic · Accepted Answer · 2017-11-05 05:18:32Z

4

Your problem is not specific to dynamic SQL - you can't refer to a placeholder within a quoted string even in a normal SQL query.

Instead you could use:

$3 * interval '1 month'

or:

($3 || ' months')::interval

The first form multiplies your supplied numeric value by a one month interval. The second constructs a string specifying the number of months and then casts it to an interval.

answered Nov 5, 2017 at 5:18

harmic

31k6 gold badges75 silver badges102 bronze badges

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

Collectives™ on Stack Overflow

How to use variable inside quoted string in postgres dynamic SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related