PLPGSQL: impossible d'utiliser des paramètres dans une requête de fonction

Question

J'essaie de créer une fonction qui renvoie tous les événements avec le numéro de semaine correspondant et le jour de la semaine où l'événement se produit à l'aide du tableau croisé et du générateur de séries.

J'ai testé que la requête réelle fonctionne à l'intérieur de la fonction si j'utilise des valeurs littérales, par exemple 2020 et 3 (numéro du mois de mars) à la place des variables.

Voici la requête de fonction que j'essaie d'utiliser:

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;

Lorsque j'essaye d'appeler la fonction dans une requête, par exemple

SELECT * FROM get_month_events(2019, 8);

J'obtiens cette erreur:

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 ne reconnaît pas le nom du paramètre dans la requête de fonction. Comment puis-je l'amener à atteindre la valeur de la variable?

Il semble que ce soit juste une erreur stupide que je n'ai pas repérée, mais jusqu'à présent, je n'ai pas été en mesure de comprendre pourquoi cela ne me laisse pas accéder à la variable.

Answer 1

Eh bien, dans votre fonction / procédure, vous passez une chaîne à la crosstabfonction de table.

Dans le contexte de la chaîne, la valeur de mthne peut pas être transmise en tant que variable dans la fonction. Vous devrez peut-être concaténer la chaîne comme ceci:

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;

Les parties pertinentes étant:

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

De cette façon, la valeur peut être concaténée avec la chaîne et exécutée dans le contexte de la crosstabfonction de table.

Solution de travail

Un exemple fonctionnel peut être trouvé dans ce violon db <>

Créer une table

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

Insérer des exemples de données

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

Créer une fonction / procédure

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;

Sélectionnez une fonction / procédure

select get_month_events(2020,03)

Production

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