PostgreSQL 影片

Loading...
Google

2008-10-16

撰寫 PL/pgSQL 函數快速指南: Part 1

PL/pgSQL 是 PostgreSQL 內置的可選預儲程序(Stored Procured)語言之一, 也是當中的效能與功能最強的。在這系列我們將學習如何撰寫 PL/pgSQL 的存儲函數/函式(Function)

PLPGSQL FUNCTION 的構造

所有的 PL/pgSQL 函數遵循的結構, 看起來都如同下面的形式。

CREATE OR REPLACE FUNCTION fnsomefunc(numtimes integer, msg text)
RETURNS text AS
$
DECLARE
strresult text;
BEGIN
strresult := '';
IF numtimes > 0 THEN
FOR i IN 1 .. numtimes LOOP
strresult := strresult || msg || E'\r\n';
END LOOP;
END IF;
RETURN strresult;
END;
$
LANGUAGE 'plpgsql' IMMUTABLE
SECURITY DEFINER
COST 10;

-- 我們呼叫這個函數它將回傳10次'hello there's'且回傳處理於單一的文本(text)欄位中.
SELECT fnsomefunc(10, 'Hello there');

這個 PL/pgSQL 函數的基本構成如下:

  1. 在函數介面宣告定義了引數(args)與返回的型別(type)。
  2. 本體(body)的封裝部份, 當前 PostgreSQL (8+) 版本首選是 $$(dollar quoting)與使用著'單引號'
  3. 在主體(body)範圍: 變數宣告 DECLARE(declaration) 的區段是可選的。
  4. 再者 BEGIN / END; 結構是定義該函數的內容。不同於 SQL 函數的選擇要求你參考到它們的順序位置例如 $1, $2, $3 等等。在 PL/pgSQL 中您能參考到它們的變數名稱。
  5. 在主體(body)之後, 諸如所有的 PostgreSQL 函數,顯著的指出預儲程序(Stored Procured)語言和標記,並且指示應該如何被快取。在這種案例中,我們已顯著的標示 IMMUTABLE 這意味著不可改變輸出的函數,如果輸入的是相同的,可以預期是相同的。其它的選擇值是 STABLE - 這意味著它將不能被變更於一個查詢式輸入範圍中,VOLATILE 如此標示的函數帶有random(),CURRENT_TIMESTAMP 可以被預計變化輸出在相同的查詢式呼叫。
  6. PostgreSQL 8.3 已投入有能力設定成本(costs)與估計行(rows)回傳的函數。對於標量函數的行不適用,我們這個簡單的例子也是同樣地。這個 COST 是相對於其他函數及預設為 100 ,除非你改變它。
  7. 另外注意到該範例的最後有個快取模式的短句 SECURITY DEFINER,這意味著該函數是運行於符合擁有人權限背景下的函數。這意味著該函數可以安全的如同擁有人所能做的任何事情,即使這樣做的人運行的函數沒有這些權限。這部分適用於不僅要 PL/pgSQL 的函數,但任何。如果這一短句排除在外,之後該函數運行是以個人為安全背景。
    對於將從 SQL Server 來的使用者 - 這是類似的概念到 SQL Server 2005 - EXECUTE AS OWNER(leaving Security definer out is equivalent to EXECUTE As CALLER in sql server)。注意:SQL Server 2005 中有一個額外的選項,稱為使用 EXECUTE As 'user_name' 但這是 PostgreSQL 所缺乏的,使您能夠執行在一個命名的使用者, 而不需要是函數的擁有者。
    對於 MySQL 使用者們,SECURITY DEFINER 存在以及與工作或多或少相同的在PostgreSQL。
  8. 幾乎所有的函數你可以寫在 PostgreSQL 抑或 SQL 中, PL/pgSQL 或者某些其它的預儲程序語言能夠使用 遞迴(recursion). 我們將在這系列的的另一個主題中示範。

有條件限制的邏輯

PL/pgSQL 對條件限制的邏輯式是成對的結構. 在上面的範例中我們看到了簡單的 IF THEN。同樣也存在於 IF .. ELSIF ..ELSIF END IF, IF ..ELSE ..END IF。我們將在上面的示例上做一個改變。

CREATE OR REPLACE FUNCTION fnsomefunc(numtimes integer, msg text)
RETURNS text AS
$
DECLARE
strresult text;
BEGIN
strresult := '';
IF numtimes = 42 THEN
strresult := '
你說得對!';
ELSIF numtimes > 0 AND numtimes < 100 THEN
FOR i IN 1 .. numtimes LOOP
strresult := strresult || msg || E'\r\n';
END LOOP;
ELSE
strresult := '
你不能這樣做。麻煩不要濫用我們的慷慨。';
IF numtimes <= 0 THEN
strresult := strresult || '你是一個 bozo
';
ELSIF numtimes > 1000 THEN
strresult := strresult || '
我不知道你以為你是誰。
你的意向失去了控制
';
END IF;
END IF;
RETURN strresult;
END;
$
LANGUAGE 'plpgsql' IMMUTABLE;

SELECT fnsomefunc(42, 'Hello there');
SELECT fnsomefunc(200, 'Hello there');
SELECT fnsomefunc(5000, 'Hello there');

控制流程

在上面的範例中,我們看到了不同的 FOR LOOP(迴圈) - 在以下是列出其他基本控制結構。第2部分中,我們將深入探討使用其中的一些。

在 PL/pgSQL 中提供的基本控制流結構是:

  • FOR somevariable IN (1 ...someendnumber) 迴圈 .. END 迴圈;
  • FOR somevariable IN REVERSE someendnumber .. 1 BY somestep 迴圈 .. END 迴圈;
  • FOR somevariable IN (somesqlquery) 迴圈 ..RETURN NEXT; .. END 迴圈;
  • 迴圈 ..logic statements EXIT .. EXIT WHEN .. CONTINUE WHEN .. END 迴圈;
  • WHILE ... 迴圈 ... END 迴圈;
  • EXCEPTION WHEN .... WHEN ..
  • 介紹在 PostgreSQL 8.3 RETURN QUERY 迴圈能被置於任何的結構或是單獨裡。
本文譯自:
http://www.postgresonline.com/journal/index.php?/archives/58-guid.html

沒有留言:

網誌存檔

PostgreSQL & Google-Analytics Running...

::Planet PostgreSQL::

PostgreSQL Information Page

PostgreSQL日記(日本 石井達夫先生Blog)

PostgreSQL News

黑喵的家 - 資料庫相關

Google 網上論壇
PostgreSQL 8 DBA 專業指南中文版
書籍內容討論與更多下載區(造訪此群組)
目錄下載: PostgreSQL_8 _DBA_Index_zh_TW.pdf (更新:2007-05-18)

全球訪客分佈圖(Google)

全球訪客分佈圖(Google)