PLPGSQL: Cannot use parameters inside a function query

Question

I'm trying to create a function that returns all events with the corresponding week number, and the day of week the event is occurring using crosstab and series generator.

I have tested that the actual query works inside the funcition if I use literal values, e.g. 2020 and 3 (March month number) in place of the variables.

Here is the function query I'm trying to use:

CREATE OR REPLACE FUNCTION get_month_events(
yr int,
mth int,
OUT week int,
OUT sun int, OUT mon int, OUT tue int, OUT wed int,
OUT thu int, OUT fri int, OUT sat int
)
RETURNS SETOF RECORD AS
$$
BEGIN
RETURN QUERY
SELECT * FROM crosstab('
SELECT
extract(week from starts) as week,
extract(dow from starts) as dow,
count(*)
FROM events
WHERE extract(month from starts) = mth
AND extract(year from starts) = yr
GROUP BY week, dow
ORDER BY week, dow',
'SELECT * FROM generate_series(0,6) AS dow'
) 
AS (
week int, 
sun int, mon int, tue int, wed int, thu int, fri int, sat int
) ORDER BY week;
END;
$$
LANGUAGE plpgsql;

When I try to call the function in a query, for example

SELECT * FROM get_month_events(2019, 8);

I'm getting this error:

ERROR: column "mth" does not exist
LINE 7: WHERE extract(month from starts) = mth
 ^
QUERY:
SELECT
extract(week from starts) as week,
extract(dow from starts) as dow,
count(*)
FROM events
WHERE extract(month from starts) = mth
AND extract(year from starts) = yr
GROUP BY week, dow
ORDER BY week, dow
CONTEXT: PL/pgSQL function get_month_events(integer,integer) line 3 at RETURN QUERY

Postgres does not recognize the parameter name inside the function query. How can I get it to reach the variable value?

It seems like it is just a stupid mistake I haven't spotted but so far I haven't been able to figure out why it does not let me access the variable.

• John K. N.: Welcome to DBA.SE. Could you please edit your question and supply the DDL of the table events and maybe two or three records? Thanks.

Answer 1

Well in your function/procedure you are passing a string to the crosstab table function.

In the context of the string the value for mth can't be passed on as a variable in the function. You might have to concatenate the string like this:

CREATE OR REPLACE FUNCTION get_month_events(
yr int,
mth int,
OUT week int,
OUT sun int, OUT mon int, OUT tue int, OUT wed int,
OUT thu int, OUT fri int, OUT sat int
)
RETURNS SETOF RECORD AS
$$
BEGIN
RETURN QUERY
SELECT * FROM crosstab('
SELECT
extract(week from starts) as week,
extract(dow from starts) as dow,
count(*)
FROM events
WHERE extract(month from starts) = ' || mth || '
AND extract(year from starts) = ' || yr || '
GROUP BY week, dow
ORDER BY week, dow',
'SELECT * FROM generate_series(0,6) AS dow'
) 
AS (
week int, 
sun int, mon int, tue int, wed int, thu int, fri int, sat int
) ORDER BY week;
END;
$$
LANGUAGE plpgsql;

The relevant parts being:

...
WHERE extract(month from starts) = ' || mth || ' -- <<< HERE
AND extract(year from starts) = ' || yr || ' -- <<< AND HERE
GROUP BY week, dow
...

This way the value can be concatenated together with the string and executed in the context of the crosstab table function.

Working Solution

A working example can be found at this db<>fiddle

Create Table

create table events(
starts date,
eventtext varchar(20)
);

Insert Sample Data

insert into events(starts, eventtext) 
values
('2020-03-01', 'test1'),
('2020-03-01', 'test2')

Create Function/Procedure

CREATE OR REPLACE FUNCTION get_month_events(
yr int,
mth int,
OUT week int,
OUT sun int, OUT mon int, OUT tue int, OUT wed int,
OUT thu int, OUT fri int, OUT sat int
)
RETURNS SETOF RECORD AS
$$
BEGIN
RETURN QUERY
SELECT * FROM crosstab('
SELECT
extract(week from starts) as week,
extract(dow from starts) as dow,
count(*)
FROM events
WHERE extract(month from starts) = ' || mth || '
AND extract(year from starts) = ' || yr || '
GROUP BY week, dow
ORDER BY week, dow',
'SELECT * FROM generate_series(0,6) AS dow'
) 
AS (
week int, 
sun int, mon int, tue int, wed int, thu int, fri int, sat int
) ORDER BY week;
END;
$$
LANGUAGE plpgsql;

Select Function/Procedure

select get_month_events(2020,03)

Output

get_month_events
----------------
(9,2,,,,,,)

• Sinipelto: Escaping the variabes outside of the string seems to work. I was wondering if the query could be done without using the strings so that escaping the variables would not be necessary?
• John K. N.: At some point the variable is going to be have to "added" (concatenated) to the string value that you are passing to the crosstab table function. Your options would be: 1.) Add the whole select string as the parameter to the function/procedure. 2.) concatenate the string into a variable inside the function/procedure and assign that variable in the crosstab( here, ....) table function.