# 我所理解的 PostgreSQL 05: PL/pgSQL 入門

{%hackmd @moment89/tXJIcGqmSI6_fJQp2hnJUA %}

## PL/pgSQL 是什麼? 和 SQL 的差異?

PL/pgSQL (Procedural Language/PostgreSQL) 是一種 [Procedural Language](https://www.postgresql.org/docs/current/xplang.html)，是 PostgreSQL 內建的語言，用來撰寫具有邏輯控制能力的 Function、Stored Procedure 與 Trigger Function 等。

相比於一般 SQL 指令，PL/pgSQL 更接近程式語言一些，它支援以下功能，彌補了 SQL 在流程控制與彈性邏輯處理上的不足。

* 變數宣告與賦值
* 條件判斷
* 迴圈控制
* 錯誤處理
* 複雜流程控制與內部邏輯封裝

## 寫 PL/pgSQL，你可能會想知道的小細節

撰寫 Function、Stored Procedure、Trigger Function 的結構都很類似，大致如下。

CREATE {物件類型} {物件名稱} (
    {輸入變數}
)
RETURNS {回傳內容} -------------------- Function 可依需求回傳資料值或 Table；Trigger Function 一律回傳 Trigger；Stored Procedure 沒有 RETURNS
LANGUAGE '{Procedural Language}' ----- 宣告所使用的 Procedural Language
SECURITY {INVOKER/DEFINER} ----------- 預設是 SECURITY INVOKER，以執行者的身份執行邏輯。改為SECURITY DEFINER則是以建立者的身份執行邏輯
AS $BODY$
DECLARE ------------------------------ 宣告內部變數區塊，但 PL/pgSQL 才有此區塊
    {宣告變數}
BEGIN
    {處理邏輯} ------------------------ 用所選擇的 Procedural Language 撰寫處理邏輯
END;
$BODY$;

結構中的 `$BODY$` 到 `$BODY$` 區塊就是 PL/pgSQL Block Structure (區塊結構)。

### `$BODY$` 是什麼?

在 PostgreSQL 中，要建立一個 Function、Stored Procedure 與 Trigger Function 時，需要在 SQL 裡面寫==一整段 PL/pgSQL 程式碼==，但因為這段程式碼勢必會包含許多特殊字如分號、引號等，會讓 SQL 的語法解讀混亂。

所以 PostgreSQL 用自訂分隔符 (Delimited String Constant) 的方式讓我們能包裹整段程式碼，避免字串中出現的符號被 SQL 誤會。

PostgreSQL 提供的自訂分隔符是用美元符號包裝 (Dollar-Quoted String Constant)，方法就是用美元符號命名一個==標籤==，可以直接用 `$$`，也可以替它命名如 `$BODY$`、`$body$`，但務必要前後一致，起訖標籤必須完全相同 (大小寫敏感)。

$$ ... $$
$function$ ... $function$
$abc123$ ... $abc123$

美元符號包裝和傳統的單引號字串包裝不同，它==不需要跳脫字元，而且支援嵌套==。

PostgreSQL 在語法層面，會把遇到的第一個「起始標籤」到下一個完全相同的「結束標籤」視為同一個字串常數。而我們可以利用不同的標籤名稱，在外層程式區塊內再寫字串常數，內層用另一個標籤包起來，就會看起來就像巢狀一樣。

$AAA$ 
...
    $aaa$
	...
    $aaa$
...
$AAA$

#### 小結

* 何時需要用自訂分隔符方式？當需要在 `CREATE FUNCTION` / `CREATE PROCEDURE` / `DO` 區塊裡面寫一大段 Procedural Languages 邏輯時 (尤其是多段邏輯時)，就建議用自訂分隔符包起來。 
* 起訖的自訂分隔符必須完全相同 (大小寫敏感，``$BODY$`` ≠ ``$body$``)。
* 外層自訂分隔符與內層自訂分隔符只要命名不同，就不會互相干擾。
* 自訂分隔符方式建議不要直接用 `$$`，若資料處理邏輯中剛好有需要寫到 '$$' 字串，會導致邏輯意外被提前結束。

### PL/pgSQL Block Structure (區塊結構)

結構很單純，就只有兩個區塊。

`DECLARE` 是選擇性的，只有需要使用變數時才需要這個區塊。
`BEGIN` ... `END` 是必要的主邏輯區塊，裡面可以撰寫流程控制邏輯、條件判斷、查詢、錯誤處理等。

[DECLARE
    ------------ 變數宣告區塊 (Optional)
]
BEGIN
    ------------ 主邏輯區塊
END;

#### 📌DECLARE 變數宣告區塊

PL/pgSQL 可使用 `DECLARE` 區塊來宣告變數，這是與 SQL 最大差異之一。

宣告變數的方法很簡單，以下範例是宣告一個 `integer` 型別的變數 `i_counter`，賦予初始值 0，以及一個 `varchar` 型別的變數 `vc_prefix`，沒有初始值。

賦值的方式，用 `:=` 和 `=` 是一樣的，都可以運作，但習慣上會使用前者。

DECLARE
    i_counter   integer := 0;
    vc_prefix   varchar;

#### 📌BEGIN ~ END 主邏輯區塊

此區塊用於撰寫邏輯，我們接著剛剛的範例繼續寫。

DECLARE
    i_counter   integer := 0;
    vc_prefix   varchar;
BEGIN
    RAISE INFO 'Week of year = %', extract(week from current_date);
    RAISE INFO 'Day of week = %', extract(dow from current_date);

    vc_prefix := concat('W', extract(week from current_date), '_');
    WHILE i_counter < extract(dow from current_date)
    LOOP
        RAISE INFO '%Day % is %', vc_prefix, i_counter + 1, current_date - extract(dow from current_date)::int + i_counter + 1;
        i_counter := i_counter + 1;
    END LOOP;
END;

好，寫完邏輯了!

但你可能會發現，這段邏輯不能直接在 PostgreSQL 查詢中執行，會顯示語法錯誤。


就如同前面介紹的，這是一段 PL/pgSQL 區塊程式碼，但我們直接拿去執行的話 PostgreSQL 不知道你是要執行哪種語言。

我們需要先將它用前面提到的自訂分隔符包裹，並利用 `DO` 語句來執行它，`DO` 語句預設會用 PL/pgSQL 去解析所執行的 Anonymous Code Block。

DO $BODY$
DECLARE
    i_counter   integer := 0;
    vc_prefix   varchar;
BEGIN
    RAISE INFO 'Week of year = %', extract(week from current_date);
    RAISE INFO 'Day of week = %', extract(dow from current_date);

    vc_prefix := concat('W', extract(week from current_date), '_');
    WHILE i_counter < extract(dow from current_date)
    LOOP
        RAISE INFO '%Day % is %', vc_prefix, i_counter + 1, current_date - extract(dow from current_date)::int + i_counter + 1;
        i_counter := i_counter + 1;
    END LOOP;
END;
$BODY$;


### 如何測試、Debug?

剛剛介紹的 `DO` 指令是拿來測試 Function、 Procedure 的好工具!
[`DO` 的用途](https://www.postgresql.org/docs/current/sql-do.html)是執行一段 Anonymous Code Block。

>[!NOTE] Anonymous Code Block
>指一段沒有名稱、沒有被包裝成 Function 或 Procedure 的 PL/pgSQL 區塊邏輯。
>這種區塊==沒有函式名稱、也沒有輸入參數或回傳值，就是單純執行一段邏輯==。

Debug 時，就可以利用 `DO` 語法搭配前一章節所介紹的 Temp Table，可以幫助我們確認過程中每一段 SQL 產生的資料。
或搭配 `RAISE` 語法，可以幫助我們確認處理過程中的一些資訊。(可以類比為 Python 的 Print、C# 的 Console.WriteLine 用法)

我們以上一章的測試資料為基礎，建立一個簡單的 Function 來 Demo。

CREATE OR REPLACE FUNCTION test.get_product_distribution(
    in_i_customer_level int
)
RETURNS TABLE (
    product_id bigint,
    product_name varchar(100),
    total_quantity bigint
)
LANGUAGE plpgsql
AS $BODY$
BEGIN

    DROP TABLE IF EXISTS tt_super_customer_orders;
    CREATE TEMP TABLE tt_super_customer_orders AS
    SELECT order_id FROM test.orders
    WHERE customer_id IN (
        SELECT customer_id FROM test.customers
        WHERE customer_level = in_i_customer_level
    );

    DROP TABLE IF EXISTS tt_total_quantity;
    CREATE TEMP TABLE tt_total_quantity AS
    SELECT
        oi.product_id,
        sum(oi.quantity) AS total_quantity
    FROM test.order_info AS oi
    JOIN tt_super_customer_orders AS o
    ON oi.order_id = o.order_id
    GROUP BY oi.product_id
    ORDER BY product_id;

    RETURN QUERY
    SELECT
        p.product_id,
        p.product_name,
        t.total_quantity
    FROM tt_total_quantity AS t
    JOIN test.products AS p
    ON t.product_id = p.product_id;

END;
$BODY$;

<center class="half">
<img src=https://hackmd.io/_uploads/SkdWnvOuxl.png width="500"/>
</center>

#### 不需要使用 `DO` 的情況

先來操作比較簡單的情況。

如果是測試 Function，因為不會異動資料庫，可以直接執行 Function 之後查詢其中使用的 Temp Table。

1. 直接查詢 Function。
<center class="half">
<img src=https://hackmd.io/_uploads/BJIEnDOdxx.png width="500"/>
</center>

2. 切換到 Messages Tab，會看到此 Function 過程中建立了哪些 Temp Table。
<center class="half">
<img src=https://hackmd.io/_uploads/ryYD3vuugl.png width="500"/>
</center>

3. 查詢該 Temp Table，可以看到其內容用於驗算、Debug。
<center class="half">
<img src=https://hackmd.io/_uploads/HkI52DOdex.png width="500"/>
</center>

#### 使用 `DO` 的情況

1. 把 Function SQL 先複製到自己慣用的文字編輯器裡。
<center class="half">
<img src=https://hackmd.io/_uploads/SJo76vd_lx.png width="500"/>
</center>

2. 把 input 參數搬到 `DECLARE` 區塊，並賦予測試數值；把 `$BODY$ DECLARE` 以前的內容都移除，改成 `DO`。
<center class="half">
<img src=https://hackmd.io/_uploads/B1wcpwO_ex.png width="500"/>
</center>

3. 畫面拉到最下方，將 `RETURN QUERY` 改為建立一張名為 `tt_return` 的 Temp Table。

    DROP TABLE IF EXISTS tt_return;
    CREATE TEMP TABLE tt_return AS

<center class="half">
<img src=https://hackmd.io/_uploads/rJDATDu_eg.png width="500"/>
</center>

5. 拿這段 SQL 去執行。
<center class="half">
<img src=https://hackmd.io/_uploads/B1T7CP_Oeg.png width="500"/>
</center>

6. 可以用 Temp Table 做資料驗證。
<center class="half">
<img src=https://hackmd.io/_uploads/HkKwRPuulg.png width="500"/>
</center>

#### 嘗試紀錄每一個階段的完成時間並輸出

1. 在想要紀錄時間點的地方加上 `RAISE` 語句。

    RAISE INFO 'Do something at %', clock_timestamp();

注意，此處建議使用 `clock_timestamp()` 而非 `CURRENT_TIMESTAMP`、`now()`。

>[!Note] 差異?
> * `CURRENT_TIMESTAMP` 和 `now()` 相等，是當前 Transaction 的開始時間。
> * `clock_timestamp()` 是真正的當前時間。
>
> 可以嘗試看看改成用 `CURRENT_TIMESTAMP` 和 `now()`，會發現在每一行輸出的時間都一模一樣。

<center class="half">
<img src=https://hackmd.io/_uploads/B1haAD_uex.png width="500"/>
</center>

2. 執行它，就可以在 Messages Tab 看到每一個時間點，知道效能瓶頸在哪囉。
<center class="half">
<img src=https://hackmd.io/_uploads/HkkgJuuulg.png width="500"/>
</center>

---

本篇介紹了 PL/pgSQL 的基礎概念，並附上了我自己當初在認識 PL/pgSQL 時有的疑問 (ex. `$BODY$`) 以及如何搭配前一篇介紹的 Temp Table 做測試驗證，希望能幫助同樣有疑問的讀者～