Referring to function parameters inside a Postgres function

Question

I have a function like this in Postgres 9.5:

CREATE OR REPLACE FUNCTION my_func (p1 date, p2 integer, p3 integer, p4 integer) RETURNS
SETOF some_datum AS $func$
 BEGIN
 RETURN QUERY
 with t as (
 select p4, postcode, p1, rent * 12 * 100 / nullif(price, 0) as result
 FROM crosstab (
 $$
 select postcode::text, indicator::int, value::float
 from my_data
 where (indicator = p2 or indicator = p3) and date = p1
 order by 1,2
 $$)
 AS ct (
 "postcode" text,
 "price" float,
 "rent" float)
 )
 select * from t where result is not null;
 END
$func$ LANGUAGE plpgsql;

When I load it with PSQL I get back the error:

ERROR: column "p2" does not exist
LINE 4: where (indicator = p2 ...
 ^
QUERY: 
 select postcode::text, indicator::int, value::float
 from my_data
 where (indicator = p2 or indicator = p3) and date = p1
 order by 1,2

I tried to prefix the p2 etc with my_func. but it doesn't work. How can I refer to a function parameter in that context?

Answer 1

Crosstab just sees a string, so send a good string.

CREATE OR REPLACE FUNCTION my_func (p1 date, p2 integer, p3 integer, p4 integer) RETURNS
SETOF some_datum AS $func$
 BEGIN
 RETURN QUERY
 WITH t AS (
 SELECT p4, postcode, p1, rent * 12 * 100 / nullif(price, 0) as result
 FROM crosstab (
 format(
 $$
 SELECT postcode::text, indicator::int, value::float
 FROM my_data
 WHERE (indicator = %s or indicator = %s)
 AND date = '%s'
 ORDER BY 1,2
 $,ドル
 p2,
 p3,
 p1
 )
 )
 AS ct (
 "postcode" text,
 "price" float,
 "rent" float)
 )
 select * from t where result is not null;
 END
$func$
LANGUAGE plpgsql;

Btw, if you're going to used named params, p1, p2, p3, etc are a bad idea. They're already 1,ドル 2,ドル 2ドル. I would suggested at least something a little easier to follow like indicator1, indicator2

• Randomize: Yes the variables have different names (I just stripped out the query as too long). Also, it should be date = %s like date = '%s'?
• Evan Carroll: @Randomize No single quotes, that's the purpose of the format(%s) it does literal escaping (%I does identifier escaping).
• Randomize: strange, I get an error without and it works with quotes.
• Evan Carroll: I take that back, it requires single quotes if the parameter types are strings. my bad.
• Evan Carroll: @Randomize only date should need the quotes btw.