我正在嘗試創建一個函式，該函式返回具有相應週數的所有事件，以及使用交叉表和系列生成器發生事件的星期幾。

如果我使用文字值（例如 2020 和 3（三月數）代替變數），我已經測試過實際查詢在函式內部有效。

這是我嘗試使用的函式查詢：

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 * FROM get_month_events(2019, 8);

我收到此錯誤：

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 無法辨識函式查詢中的參數名稱。我怎樣才能讓它達到變數值？

似乎這只是一個我沒有發現的愚蠢錯誤，但到目前為止我還無法弄清楚為什麼它不允許我訪問該變數。

那麼在您的函式/過程中，您正在將一個字元串傳遞給crosstab表函式。

在字元串的上下文中，值mth不能作為函式中的變數傳遞。您可能必須像這樣連接字元串：

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;

相關部分是：

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

這樣，值可以與字元串連接在一起並在crosstab表函式的上下文中執行。

工作解決方案

可以在這個db<>fiddle找到一個工作範例

創建表

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

插入樣本數據

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

創建函式/過程

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 get_month_events(2020,03)

輸出

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

引用自：https://dba.stackexchange.com/questions/275088