PLPGSQL: Bir işlev sorgusu içinde parametreler kullanılamaz

Question

Karşılık gelen hafta numarasına sahip tüm olayları ve çapraz tablo ve seri oluşturucu kullanarak olayın meydana geldiği haftanın gününü döndüren bir işlev oluşturmaya çalışıyorum.

Değişkenler yerine 2020 ve 3 (Mart ay numarası) gibi değişmez değerler kullanırsam gerçek sorgunun işlev içinde çalıştığını test ettim.

İşte kullanmaya çalıştığım işlev sorgusu:

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;

Örneğin bir sorguda işlevi çağırmaya çalıştığımda

SELECT * FROM get_month_events(2019, 8);

Bu hatayı alıyorum:

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, işlev sorgusu içindeki parametre adını tanımıyor. Değişken değere ulaşmasını nasıl sağlayabilirim?

Görünüşe göre bu fark etmediğim aptalca bir hata ama şimdiye kadar neden değişkene erişmeme izin vermediğini anlayamadım.

Answer 1

İşlev / yordamınızda, crosstabtablo işlevine bir dizge iletiyorsunuz.

Dize bağlamında, değeri mthişlevde bir değişken olarak aktarılamaz. Dizeyi şu şekilde birleştirmeniz gerekebilir:

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;

İlgili kısımlar:

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

Bu şekilde, değer dizeyle birleştirilebilir ve crosstabtablo işlevi bağlamında çalıştırılabilir .

Çalışma Çözümü

Bu db <> keman çalışmasında bir örnek bulunabilir

Tablo Oluştur

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

Örnek Verileri Girin

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

İşlev / Prosedür Oluştur

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;

Fonksiyonu / Prosedürü Seçin

select get_month_events(2020,03)

Çıktı

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