PostgreSQL檢視函式與觸發器

PostgreSQL 提供檢視、函式和觸發器等機制，有效簡化資料函式庫操作並提升效率。檢視可限制資料存取，也能作為資料處理的捷徑，例如透過 employees_tax_dept 檢視即可刪除特定員薪水料。此外，建立自訂函式能避免重複程式碼，例如 percent_change() 函式可簡化百分比變化計算，並應用於人口普查資料分析。Stored Procedure 能執行不傳回值的更新操作，例如根據教師到職日更新個人休假天數。PostgreSQL 也支援以 PL/Python 建立函式，例如利用 Python 的正規表示式功能移除字串字尾。最後，觸發器可在特定事件發生時自動執行函式，例如記錄學生成績表更動，進一步提升資料函式倉管理的自動化程度。

使用 employees_tax_dept 檢視刪除資料列

現在，讓我們來探討如何使用檢視刪除資料列。同樣地，對於可以影響的資料也有相關的限制。例如，如果 Suzanne Le Gere 從其他公司獲得更好的工作機會並決定離職，您可以透過 employees_tax_dept 檢視從 employees 表格中移除她的資料。第 17-10 節展示了標準 DELETE 語法中的查詢。

DELETE FROM employees_tax_dept
WHERE emp_id = 5;

第 17-10 節：透過 employees_tax_dept 檢視刪除資料列

執行查詢，PostgreSQL 應該會回應 DELETE 1。然而，當您嘗試刪除稅務部門以外其他部門員工的資料列時，PostgreSQL 不會允許並回報 DELETE 0。

總之，檢視不僅能控制資料的存取，還能為處理資料提供捷徑。接下來，讓我們探討如何使用函式來節省時間和輸入。

建立自訂函式和程式

在這本文中，您已經使用過諸如 upper() 用於大寫字母或 sum() 用於數字相加等函式。在這些函式背後，有大量（有時是複雜的）程式碼執行一系列操作，並可能根據函式的工作傳回回應。我們將避免複雜的程式碼，但將建立一些基本的函式，您可以用作自己想法的起點。即使是簡單的函式也可以幫助您避免重複程式碼。

本文的大部分語法都是 PostgreSQL 特有的，它支援使用者定義的函式和程式（兩者之間的差異很微妙，我將給出兩者的範例）。您可以使用純 SQL 定義函式和程式，但也可以選擇其他選項。其中一個是 PostgreSQL 特有的程式語言，稱為 PL/pgSQL，它新增了標準 SQL 中沒有的功能，例如邏輯控制結構（IF … THEN … ELSE）。其他選項包括 Python 和 R 程式語言的 PL/Python 和 PL/R。

請注意，包括 Microsoft SQL Server、Oracle 和 MySQL 在內的主要資料函式庫系統都實作了自己的函式和程式版本。如果您使用的是其他資料函式倉管理系統，本文將有助於理解與函式相關的概念，但您需要檢視資料函式庫的檔案以瞭解其對函式實作的具體細節。

建立 percent_change() 函式

函式處理資料並傳回一個值。作為範例，讓我們寫一個函式來簡化資料分析中的一個基本操作：計算兩個值之間的百分比變化。在第 6 章中，您瞭解到我們用以下公式表示百分比變化：

百分比變化 = (新數值 - 舊數值) / 舊數值

與其每次需要時都寫這個公式，我們可以建立一個名為 percent_change() 的函式，它接受新舊數值作為輸入，並傳回四捨五入到使用者指定的小數位數的結果。讓我們逐步瀏覽第 17-11 節中的程式碼，以瞭解如何宣告一個使用 SQL 的簡單函式。

CREATE OR REPLACE FUNCTION 
percent_change(new_value numeric, 
               old_value numeric, 
               decimal_places integer DEFAULT 1)
RETURNS numeric AS
'SELECT round(((new_value - old_value) / old_value) * 100, decimal_places);'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;

第 17-11 節：建立 percent_change() 函式

內容解密：

1. CREATE OR REPLACE FUNCTION：建立或替換一個函式。
2. percent_change(new_value numeric, old_value numeric, decimal_places integer DEFAULT 1)：定義函式名稱和引數，其中 decimal_places 引數預設為 1。
3. RETURNS numeric AS：指定函式傳回的資料型別為 numeric。
4. 'SELECT round(((new_value - old_value) / old_value) * 100, decimal_places);'：函式主體，計算百分比變化並四捨五入到指定的小數位數。
5. LANGUAGE SQL：指定函式使用的語言為 SQL。
6. IMMUTABLE：表示函式不會修改資料函式庫，並且對於給定的引數總是傳回相同的結果。
7. RETURNS NULL ON NULL INPUT：如果任何非預設輸入為 NULL，則函式傳回 NULL。

使用 pgAdmin 執行程式碼以建立 percent_change() 函式。伺服器應該會回應訊息 CREATE FUNCTION。

使用 percent_change() 函式

要測試新的 percent_change() 函式，可以單獨使用 SELECT，如第 17-12 節所示。

SELECT percent_change(110, 108, 2);

第 17-12 節：測試 percent_change() 函式

這個範例使用 110 作為新數值，108 作為舊數值，2 作為期望的小數位數。執行程式碼；結果應該如下所示：

percent_change
---
-
---
-
---
-
---
-
1.85

結果告訴我們，從 108 到 110 有 1.85% 的增長。嘗試使用其他數字來檢視結果如何變化。同時，嘗試更改 decimal_places 引數的值，包括 0，或省略它，以檢視這如何影響輸出。

我們建立這個函式是為了避免在查詢中寫完整的百分比變化公式。讓我們使用它來計算百分比變化，使用第 7 章中編寫的人口普查估計人口變化查詢的一個版本，如第 17-13 節所示。

SELECT c2019.county_name,
       c2019.state_name,
       c2019.pop_est_2019 AS pop_2019,
       percent_change(c2019.pop_est_2019, c2010.estimates_base_2010) AS pct_chg_func,
       round((c2019.pop_est_2019::numeric - c2010.estimates_base_2010) / c2010.estimates_base_2010 * 100, 1) AS pct_change_formula
FROM us_counties_pop_est_2019 AS c2019
JOIN us_counties_pop_est_2010 AS c2010
ON c2019.state_fips = c2010.state_fips
AND c2019.county_fips = c2010.county_fips
ORDER BY pct_chg_func DESC
LIMIT 5;

第 17-13 節：在人口普查資料上測試 percent_change()

內容解密：

1. SELECT 陳述式選擇了縣名、州名、2019 年估計人口、percent_change() 函式計算的百分比變化，以及直接使用公式計算的百分比變化。
2. percent_change(c2019.pop_est_2019, c2010.estimates_base_2010) 使用 percent_change() 函式計算從 2010 年到 2019 年的人口百分比變化。
3. round((c2019.pop_est_2019::numeric - c2010.estimates_base_2010) / c2010.estimates_base_2010 * 100, 1) 直接使用公式計算百分比變化並四捨五入到小數點後一位。
4. 資料來自 us_counties_pop_est_2019 和 us_counties_pop_est_2010 表格，透過州和縣的 FIPS 程式碼進行連線。
5. 結果按 pct_chg_func（即 percent_change() 的結果）降序排序，並限制輸出前 5 行。

使用percent_change()函式計算人口變化率

在前面的章節中，我們已經瞭解如何使用SQL函式來簡化資料分析。Listing 17-13展示瞭如何修改原始查詢以新增percent_change()函式作為SELECT陳述式中的一列。同時，我們也包含了明確的百分比變化公式，以便比較結果。作為輸入，我們使用2019年的人口估計列（c2019.pop_est_2019）作為新數字，使用2010年的估計基數（c2010.estimates_base_2010）作為舊數字。

查詢結果應該顯示人口變化百分比最大的五個縣，並且函式的結果應該與直接輸入查詢的公式結果相符。注意，pct_chg_func列中的每個值都有一個小數位數，這是函式的預設值，因為我們沒有提供可選的第三個引數。以下是同時包含函式和公式的結果：

| county_name | state_name | pop_2019 | pct_chg_func | pct_chg_formula | |

-|

|

|

|

–| | McKenzie County| North Dakota | 15024 | 136.3 | 136.3 | | Loving County | Texas | 169 | 106.1 | 106.1 | | Williams County| North Dakota | 37589 | 67.8 | 67.8 | | Hays County | Texas | 230191 | 46.5 | 46.5 | | Wasatch County | Utah | 34091 | 44.9 | 44.9 |

內容解密：

1. percent_change()函式用於計算兩個數字之間的百分比變化。
2. 使用2019年的人口估計和2010年的估計基數作為輸入。
3. 結果顯示人口變化百分比最大的五個縣。
4. pct_chg_func列顯示使用percent_change()函式計算的結果。
5. pct_chg_formula列顯示使用明確的百分比變化公式計算的結果。

現在我們知道percent_change()函式按預期工作，我們可以在需要進行該計算時使用它，這比寫出公式要快得多！

使用程式更新資料

在PostgreSQL中，程式（Procedure）是函式的近親，但有一些顯著的差異。程式和函式都可以執行不傳回值的資料操作，例如更新。程式不具有傳回值的子句，而函式則具有。此外，程式可以包含事務命令，如COMMIT和ROLLBACK，而函式則不能。許多資料函式倉管理器都實作了程式，有時也被稱為儲存過程。PostgreSQL從版本11開始增加了程式，並且是SQL標準的一部分，儘管PostgreSQL的語法並不完全相容。

為教師表新增personal_days列

首先，我們需要在teachers表中新增一個新的列personal_days，用於儲存教師的個人休假天數。使用Listing 17-14中的程式碼：

ALTER TABLE teachers ADD COLUMN personal_days integer;
SELECT first_name, last_name, hire_date, personal_days FROM teachers;

內容解密：

1. ALTER TABLE陳述式用於修改現有的表結構。
2. ADD COLUMN子句用於新增新的列personal_days。
3. SELECT陳述式用於檢視更新後的表資料。

執行後，你應該會看到六行資料，並且personal_days列中包含NULL值，因為我們尚未插入任何資料。

建立update_personal_days()程式

接下來，我們將建立一個名為update_personal_days()的程式，用於根據教師的聘用日期更新personal_days列。使用Listing 17-15中的程式碼：

CREATE OR REPLACE PROCEDURE update_personal_days()
AS $$
BEGIN
    UPDATE teachers
    SET personal_days = 
        CASE 
            WHEN (now() - hire_date) >= '10 years'::interval AND (now() - hire_date) < '15 years'::interval THEN 4
            WHEN (now() - hire_date) >= '15 years'::interval AND (now() - hire_date) < '20 years'::interval THEN 5
            WHEN (now() - hire_date) >= '20 years'::interval AND (now() - hire_date) < '25 years'::interval THEN 6
            WHEN (now() - hire_date) >= '25 years'::interval THEN 7
            ELSE 3
        END;
    RAISE NOTICE 'personal_days updated!';
END;
$$ LANGUAGE plpgsql;

內容解密：

1. CREATE OR REPLACE PROCEDURE陳述式用於建立或替換現有的程式。
2. AS $$開始定義程式的主體，使用美元引號（$$）來標記字串的開始和結束。
3. BEGIN ... END;塊用於定義程式的程式碼邏輯。
4. UPDATE陳述式使用CASE陳述式根據教師的聘用日期更新personal_days列。
5. RAISE NOTICE陳述式用於顯示通知訊息，表示程式已完成更新。
6. LANGUAGE plpgsql指定程式使用的語言是PL/pgSQL。

要呼叫該程式，可以使用CALL命令：

CALL update_personal_days();

這樣，personal_days列就會根據教師的聘用日期被更新。

自動化資料函式庫操作：函式與觸發器

在資料函式倉管理中，自動化是提高效率和準確性的關鍵。PostgreSQL 提供了強大的功能來實作自動化，包括函式和觸發器。本篇文章將探討如何使用這些功能來自動化資料函式庫操作。

使用函式自動更新資料

函式是資料函式庫中用於執行特定任務的可重複使用的程式碼區塊。我們可以建立一個函式來自動更新員工的年假天數。

更新員工年假天數的函式

CREATE OR REPLACE FUNCTION update_personal_days()
RETURNS void AS $$
BEGIN
    UPDATE employees
    SET personal_days = EXTRACT(YEAR FROM AGE(CURRENT_DATE, hire_date)) + 1;
END;
$$ LANGUAGE plpgsql;

內容解密：

1. CREATE OR REPLACE FUNCTION update_personal_days()：建立或替換一個名為 update_personal_days 的函式。
2. RETURNS void：表示該函式不傳回任何值。
3. UPDATE employees SET personal_days = ...：更新 employees 表中的 personal_days 列，根據員工的入職日期計算年假天數。
4. EXTRACT(YEAR FROM AGE(CURRENT_DATE, hire_date)) + 1：計算員工的服務年數，並加一作為年假天數。

呼叫此函式即可更新所有員工的年假天數。

CALL update_personal_days();

使用 Python 語言建立函式

PostgreSQL 支援使用多種程式語言建立函式，包括 Python。我們可以使用 Python 來建立一個函式，移除字串中的特定字尾。

啟用 PL/Python

CREATE EXTENSION plpython3u;

使用 PL/Python 建立函式

CREATE OR REPLACE FUNCTION trim_county(input_string text)
RETURNS text AS $$
import re
cleaned = re.sub(r' County', '', input_string)
return cleaned
$$ LANGUAGE plpython3u;

內容解密：

1. import re：匯入 Python 的正規表示式模組。
2. cleaned = re.sub(r' County', '', input_string)：使用正規表示式替換 input_string 中的 " County" 為空字串。
3. return cleaned：傳回處理後的字串。

測試此函式的效果：

SELECT county_name, trim_county(county_name)
FROM us_counties_pop_est_2019
ORDER BY state_fips, county_fips
LIMIT 5;

自動化資料函式庫操作：觸發器

觸發器是一種特殊的函式，當特定的事件（如 INSERT、UPDATE 或 DELETE）發生在表或檢視上時自動執行。我們可以使用觸發器來自動記錄對學生成績表的修改。

建立成績表和歷史記錄表

CREATE TABLE grades (
    student_id bigint,
    course_id bigint,
    course text NOT NULL,
    grade text NOT NULL,
    PRIMARY KEY (student_id, course_id)
);

CREATE TABLE grades_history (
    -- columns to record changes
);

建立觸發器函式

CREATE OR REPLACE FUNCTION record_if_grade_changed()
RETURNS TRIGGER AS $$
BEGIN
    -- logic to record changes
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

內容解密：

1. CREATE OR REPLACE FUNCTION record_if_grade_changed()：建立或替換一個名為 record_if_grade_changed 的觸發器函式。
2. RETURNS TRIGGER：表示該函式是觸發器函式。
3. -- logic to record changes：在此處新增記錄變更的邏輯。

建立觸發器

CREATE TRIGGER grades_update
AFTER UPDATE ON grades
FOR EACH ROW
EXECUTE FUNCTION record_if_grade_changed();

內容解密：

1. CREATE TRIGGER grades_update：建立一個名為 grades_update 的觸發器。
2. AFTER UPDATE ON grades：指定觸發器在 grades 表上的 UPDATE 操作後觸發。
3. FOR EACH ROW：指定觸發器對每一行受影響的資料執行。
4. EXECUTE FUNCTION record_if_grade_changed()：指定觸發器執行的函式。

透過使用函式和觸發器，我們可以有效地自動化資料函式庫操作，提高資料管理的效率和準確性。