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