1

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?

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.