postgresql – PLPGSQL: Cannot use parameters inside a function query


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.

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,,,,,,)