常見 PL/pgSQL 函式錯誤大解析

今天我們來拆解一下寫 function 時常見的錯誤、為什麼會發生，還有怎麼解決。畢竟只有 debug 過，才會變成真正的 coding 大師！Let's debug it！

剛開始學 PL/pgSQL 時，寫 function 真的會讓人頭大。就算是 PostgreSQL 的老手，也常常會踩到坑。我們一個一個來看。

1. 漏掉 RETURNS 關鍵字

PL/pgSQL 對 function 的定義很嚴格。最常見的錯誤之一，就是忘記寫 function 要回傳什麼型別。來看個例子：

-- 錯誤：缺少 RETURNS 關鍵字
CREATE FUNCTION incorrect_function() AS $$
BEGIN
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

PostgreSQL 會搞不清楚這個 function 應該回傳什麼。RETURNS 是語法裡一定要有的，指定回傳資料型別（像是 RETURNS INT、RETURNS TEXT，或 RETURNS VOID）。

修正方式： 加上 RETURNS 跟型別：

CREATE FUNCTION correct_function() RETURNS INT AS $$
BEGIN
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

2. 沒用 RETURN 回傳結果

新手常常會忘記，在 PL/pgSQL 裡要回傳結果一定要寫 RETURN。像這樣：

-- 錯誤：缺少 RETURN
CREATE FUNCTION missing_return() RETURNS TEXT AS $$
BEGIN
    'Hello, World!'; -- 只是寫個字串，沒真的回傳
END;
$$ LANGUAGE plpgsql;

這裡 'Hello, World!' 只是單純寫出來，沒真的回傳。PostgreSQL 會覺得沒東西回傳，直接報錯。

修正方式： 加上 RETURN：

CREATE FUNCTION fixed_return() RETURNS TEXT AS $$
BEGIN
    RETURN 'Hello, World!';
END;
$$ LANGUAGE plpgsql;

3. 寫入沒宣告的變數

在 PL/pgSQL 裡，變數一定要先在 DECLARE 區塊宣告。像這樣：

-- 錯誤：my_var 變數沒宣告
CREATE FUNCTION missing_variable() RETURNS VOID AS $$
BEGIN
    my_var := 'Hello, World!';
END;
$$ LANGUAGE plpgsql;

PostgreSQL 根本不知道 my_var 是誰，因為你沒在 DECLARE 宣告。

修正方式： 記得在 DECLARE 宣告變數：

CREATE FUNCTION declared_variable() RETURNS VOID AS $$
DECLARE
    my_var TEXT;
BEGIN
    my_var := 'Hello, World!';
END;
$$ LANGUAGE plpgsql;

4. 錯用 VOID 回傳型別

VOID 代表這個 function 不會回傳任何資料。有時候大家會在 VOID function 裡寫 RETURN，這樣會出錯：

-- 錯誤：VOID function 裡 RETURN 了值
CREATE FUNCTION void_example() RETURNS VOID AS $$
BEGIN
    RETURN 1; -- 不可以回傳值
END;
$$ LANGUAGE plpgsql;

回傳型別是 VOID 的 function，不能回傳任何值。RETURN 可以寫，但後面不能帶值。

修正方式： 要嘛拿掉 RETURN，要嘛只寫 RETURN 不帶值：

CREATE FUNCTION correct_void() RETURNS VOID AS $$
BEGIN
    -- 只做事，不回傳
    RAISE NOTICE 'This function does not return anything';
    RETURN; -- 結束 function
END;
$$ LANGUAGE plpgsql;

5. 用錯 RAISE debug

PL/pgSQL debug 很常用 RAISE NOTICE。但格式或變數寫錯就會出錯。

例子：

-- 錯誤：格式錯誤
CREATE FUNCTION debug_example() RETURNS VOID AS $$
BEGIN
    RAISE NOTICE 'The value is %'; -- 少了變數
END;
$$ LANGUAGE plpgsql;

RAISE 看到 %，就會等你給變數或值。如果沒給，PostgreSQL 會報錯。

修正方式： 記得把變數或值寫對：

CREATE FUNCTION fixed_debug() RETURNS VOID AS $$
DECLARE
    my_var TEXT := 'PostgreSQL';
BEGIN
    RAISE NOTICE 'The value is %', my_var; -- 有給變數
END;
$$ LANGUAGE plpgsql;

6. 變數名跟欄位名撞名

如果變數名跟資料表欄位名一樣，會有意想不到的結果。像這樣：

-- 錯誤：變數名跟欄位名衝突
CREATE FUNCTION name_conflict() RETURNS TEXT AS $$
DECLARE
    name TEXT;
BEGIN
    SELECT name INTO name FROM students LIMIT 1; -- 到底是哪個 name？
    RETURN name;
END;
$$ LANGUAGE plpgsql;

PL/pgSQL 會優先用變數名，如果撞名就會怪怪的。

修正方式： 用 table alias 或避開撞名。

CREATE FUNCTION fixed_conflict() RETURNS TEXT AS $$
DECLARE
    student_name TEXT;
BEGIN
    SELECT s.name INTO student_name FROM students s LIMIT 1;
    RETURN student_name;
END;
$$ LANGUAGE plpgsql;

7. 在迴圈裡執行錯誤的查詢

在迴圈裡跑 SQL 查詢很容易出錯。像這樣：

-- 錯誤：迴圈裡的查詢寫錯
CREATE FUNCTION cycle_error() RETURNS VOID AS $$
BEGIN
    FOR rec IN SELECT * FROM students LOOP
        EXECUTE 'UPDATE students SET active = TRUE WHERE id = ' || rec.id;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

SQL 注入警告！用字串拼接 SQL 查詢超危險，會有漏洞。

修正方式，用參數：

CREATE FUNCTION safe_cycle() RETURNS VOID AS $$
BEGIN
    FOR rec IN SELECT * FROM students LOOP
        EXECUTE 'UPDATE students SET active = TRUE WHERE id = $1' USING rec.id;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

8. 資料型別錯誤

錯誤例子：

-- 錯誤：資料型別不符
CREATE FUNCTION type_error() RETURNS INT AS $$
DECLARE
    my_var TEXT := 'not_a_number';
BEGIN
    RETURN my_var; -- 回傳 TEXT 給 INT，會錯
END;
$$ LANGUAGE plpgsql;

PostgreSQL 要 INT，你給 TEXT，型別不對就會報錯。

怎麼修？型別要對，或是做型別轉換：

CREATE FUNCTION type_correct() RETURNS INT AS $$
DECLARE
    my_var TEXT := '42';
BEGIN
    RETURN my_var::INT; -- 把字串轉成數字
END;
$$ LANGUAGE plpgsql;

最佳實踐與小撇步

• 把複雜的 function 拆小。這樣 debug 跟測試都比較簡單。
• 多寫註解，特別是複雜的地方。
• 先用小資料測試 function，不要一開始就對正式資料表下手。
• 用 RAISE NOTICE debug，看 function 執行到哪。
• 避免 SQL 注入：查詢時用參數。

-- 用 RAISE debug
DO $$
DECLARE
    total_students INT;
BEGIN
    SELECT COUNT(*) INTO total_students FROM students;
    RAISE NOTICE 'Total students: %', total_students; -- debug 訊息
END;
$$;

這些小撇步可以幫你少踩很多 PL/pgSQL 的地雷，讓你 coding 更順！