PostgreSQL提供了許多好用的函數，例如之前所提的聚合函數、日期與時間函數等等，而除了內建的函數之外，許多程式語言都有撰寫自訂函數的功能，PostgreSQL同樣也可以撰寫，今天就一步一步來認識自訂函數的寫法，由於本篇會帶有一些程式語言觀念的內容，若沒有任何程式語言基礎的話則會略為吃力。

創建函數的基本語法

1. CREATE 創建
2. OR REPLACE 如果已經存在這個函數，則取代。(更新)
3. FUNCTION 函數
4. fnSubstr(VARCHAR, INTEGER, INTEGER) 函數名稱與參數的資料型別
5. RETURNS VARCHAR 告訴回傳的資料型別，要稍微注意是RETURNS，有加S。
6. LANGUAGE plpgsql 使用哪一種程式語言創建函數 (不是只有plpgsql可以創建)
7. AS $$ 用來隔離程式碼
8. $1, $2, $3 … 代表參數的順序

CREATE OR REPLACE FUNCTION fnSubstr(VARCHAR, INTEGER, INTEGER) 
RETURNS VARCHAR 
LANGUAGE plpgsql
AS 
$$
BEGIN 
	RETURN SUBSTRING($1, $2, $3); 
END;
$$

SELECT fnSubstr('Hello World!', 1, 5); --Hello

使用別名

• DECLARE 宣告變數名稱
• ALIAS FOR 參數的別名

CREATE OR REPLACE FUNCTION fnSubstr1(VARCHAR, INTEGER, INTEGER) 
RETURNS VARCHAR
LANGUAGE plpgsql
AS 
$$
DECLARE word ALIAS FOR $1;
				startPos ALIAS FOR $2;
				len ALIAS FOR $3;
BEGIN 
	-- 可以使用變數名稱了
	RETURN SUBSTRING(word, startPos, len); 
END;
$$
SELECT fnSubstr1('Hello World!', 7, 5); --World

給參數名稱

-- 直接給參數名稱
CREATE OR REPLACE FUNCTION fnSubstr2(word VARCHAR,startPos INTEGER, len INTEGER) 
RETURNS VARCHAR
LANGUAGE plpgsql
AS 
$$
BEGIN 
	-- 直接使用參數名稱
	RETURN SUBSTRING(word, startPos, len); 
END;
$$
SELECT fnSubstr2('Hello World!', 1, 11); --Hello World

加上邏輯判斷(IF / ELSE)

函數可以依傳進來的參數進行條件的判斷，下例會依據傳入的值是否為NULL而給出不同的回傳值。

CREATE OR REPLACE FUNCTION fnMakeFull(firstname VARCHAR,lastname VARCHAR)
RETURNS VARCHAR
LANGUAGE plpgsql
AS $$
BEGIN 
		-- 許多程式語言會有的IF / ELSE
    IF firstName IS NULL AND lastName IS NULL THEN
        RETURN NULL;
    ELSEIF firstName IS NULL AND lastName IS NOT NULL THEN
        RETURN lastName;        
    ELSEIF firstName IS NOT NULL AND lastName IS NULL THEN
        RETURN firstName;
    ELSE 
        -- 方法1 : RETURN firstName || ' ' || lastName;
        -- 方法2 : RETURN concat(firstName, ' ', lastName);
        RETURN  concat_ws(' ', firstName, lastName);
    END IF;
END;
$$;

使用陣列參數

我們製作一個算平均的函數，傳入值是陣列，這個例子用到了迴圈FOREACH來計算總值跟計數。

CREATE OR REPLACE FUNCTION fnMean(NUMERIC[])
RETURNS NUMERIC
LANGUAGE plpgsql;
AS
$$
DECLARE total NUMERIC := 0;
        val NUMERIC;
        cnt INT := 0;
        list ALIAS FOR $1;
BEGIN 
    FOREACH val IN array list 
    LOOP
        total := total + val;
        cnt := cnt +1;
    END LOOP;
    
    RETURN total/cnt;
END;
$$

SELECT * FROM fnMean(ARRAY[1,2,3]); -- 2

回傳TABLE的資料

這個例子是假設有一個table叫books，我們現在要取出資料，就可以使用函數來取得，好處是我們只要給函數的參數值不同就能夠取出不同的資料，是一種簡潔導向或靈活導向的方式。

CREATE OR REPLACE FUNCTION fnGetBookByYear(yr INTEGER)
RETURNS TABLE
(
    book_id INTEGER,
    book_name VARCHAR(60),
    year_released INTEGER
)
AS
$$
BEGIN 
    RETURN QUERY
    SELECT book_id,
           book_name,
           year_released
    FROM books
    WHERE year_released = yr;
END;
$$
LANGUAGE plpgsql;