PLPGSQL: não é possível usar parâmetros dentro de uma consulta de função

Question

Estou tentando criar uma função que retorna todos os eventos com o número da semana correspondente e o dia da semana em que o evento está ocorrendo usando crosstab e gerador de série.

Eu testei se a consulta real funciona dentro da função se eu usar valores literais, por exemplo, 2020 e 3 (número do mês de março) no lugar das variáveis.

Aqui está a consulta de função que estou tentando usar:

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;

Quando tento chamar a função em uma consulta, por exemplo

SELECT * FROM get_month_events(2019, 8);

Estou recebendo este erro:

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 não reconhece o nome do parâmetro dentro da consulta da função. Como faço para que ele alcance o valor da variável?

Parece que é apenas um erro estúpido que não detectei, mas até agora não consegui descobrir por que ele não me permite acessar a variável.

Answer 1

Bem, em sua função / procedimento você está passando uma string para a crosstabfunção de tabela.

No contexto da string, o valor de mthnão pode ser passado como uma variável na função. Você pode ter que concatenar a string desta forma:

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;

As partes relevantes são:

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

Desta forma, o valor pode ser concatenado junto com a string e executado no contexto da crosstabfunção da tabela.

Solução de Trabalho

Um exemplo prático pode ser encontrado neste db <> fiddle

Criar a tabela

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

Inserir dados de amostra

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

Criar Função / Procedimento

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;

Selecione Função / Procedimento

select get_month_events(2020,03)

Resultado

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