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?
