PostgreSQL PL/pgSQL開發(fā)提示

2021-09-03 17:38 更新
42.12.1. 處理引號
42.12.2. 額外的編譯時和運行時檢查

在PL/pgSQL中進行開發(fā)的一種好方法是使用你自己選擇的文本編輯器來創(chuàng)建函數(shù)，并且在另一個窗口中使用psql來載入并且測試那些函數(shù)。如果你正在這樣做，使用CREATE OR REPLACE FUNCTION來編寫函數(shù)是一個好主意。用那種方式你只需要重載該文件來更新函數(shù)的定義。例如：

CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $$
          ....
$$ LANGUAGE plpgsql;

在運行psql期間，你可以用下面的命令載入或者重載這樣一個函數(shù)定義文件：

\i filename.sql

并且接著立即發(fā)出 SQL 命令來測試該函數(shù)。

另一種在PL/pgSQL中開發(fā)的方式是用一個 GUI 數(shù)據(jù)庫訪問工具，它能方便對過程語言的開發(fā)。這種工具的一個例子是pgAdmin。這些工具通常提供方便的特性，例如轉義單引號以及便于重新創(chuàng)建和調試函數(shù)。

42.12.1. 處理引號

一個PL/pgSQL函數(shù)的代碼在一個CREATE FUNCTION中被指定為一個字符串。如果你用通常的方式把該字符串寫在單引號中間，那么該函數(shù)體中的任何單引號都必須被雙寫；同樣任何反斜線也必須被雙寫（假定使用了轉義字符串語法）。雙寫引號最多有點冗長，并且在更復雜的情況中代碼會變得完全無法理解，因為你很容易發(fā)現(xiàn)你需要半打或者更多相鄰的引號。我們推薦你轉而把函數(shù)體寫成一個 “美元引用”的字符串（見第 4.1.2.4 節(jié)）。在美元引用方法中，你從不需要雙寫任何引號。但是要注意為你需要的每一層嵌套選擇一個不同的美元引用定界符。例如，你可能把CREATE FUNCTION命令寫成：

CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $PROC$ .... $PROC$ LANGUAGE plpgsql;

在這里面，你可以在 SQL 命令中為簡單字符串使用引號并且用$$來界定被你組裝成字符串的 SQL 命令片段。如果你需要引用包括$$的文本，你可以使用$Q$等等。

下列圖表展示了在寫沒有美元引用的引號時需要做什么。在將之前用美元引用的代碼翻譯成更容易理解的代碼時，它們會有所幫助。

1 個引號

用來開始和結束函數(shù)體，例如：

CREATE FUNCTION foo() RETURNS integer AS '
          ....
' LANGUAGE plpgsql;

在一個單引號引用的函數(shù)體中的任何位置，引號必須成對出現(xiàn)。

2 個引號

用于函數(shù)體內的字符串，例如：

a_output := ''Blah'';
SELECT * FROM users WHERE f_name=''foobar'';

在美元引用方法中，你只需要寫：

a_output := 'Blah';
SELECT * FROM users WHERE f_name='foobar';

這恰好就是PL/pgSQL在兩種情況中會看到的。

4 個引號

當你在函數(shù)內的一個字符串常量中需要一個單引號時，例如：

a_output := a_output || '' AND name LIKE ''''foobar'''' AND xyz''

實際會被追加到a_output的值將是： AND name LIKE 'foobar' AND xyz。

在美元引用方法中，你可以寫：

a_output := a_output || $$ AND name LIKE 'foobar' AND xyz$$

要小心在這周圍的任何美元引用定界符不只是$$。

6 個引號

當在函數(shù)體內的一個字符串中的一個單引號與該字符串常量末尾相鄰，例如：

a_output := a_output || '' AND name LIKE ''''foobar''''''

被追加到a_output的值則將是： AND name LIKE 'foobar'。

在美元引用方法中，這會變成：

a_output := a_output || $$ AND name LIKE 'foobar'$$

10 個引號

當你想在一個字符串常量（占 8 個引號）中有兩個單引號時并且這會挨著該字符串常量的末尾（另外 2 個）。如果你正在寫一個產(chǎn)生其他函數(shù)的函數(shù)（如例 42.10中），你將很可能只需要這種。例如：

a_output := a_output || '' if v_'' ||
    referrer_keys.kind || '' like ''''''''''
    || referrer_keys.key_string || ''''''''''
    then return ''''''  || referrer_keys.referrer_type
    || ''''''; end if;'';

a_output的值將是：

if v_... like ''...'' then return ''...''; end if;

在美元引用方法中，這會變成：

a_output := a_output || $$ if v_$$ || referrer_keys.kind || $$ like '$$
    || referrer_keys.key_string || $$'
    then return '$$  || referrer_keys.referrer_type
    || $$'; end if;$$;

這里我們假定我們只需要把單引號放在a_output中，因為在使用前它將被再引用。

42.12.2. 額外的編譯時和運行時檢查

為了輔助用戶在一些簡單但常見的問題產(chǎn)生危害之前找到它們， PL/pgSQL提供了額外的檢查。當被啟用時， 根據(jù)配置，它們可以在一個函數(shù)的編譯期間被用來發(fā)出 WARNING或者ERROR。一個已經(jīng)收到了 WARNING的函數(shù)可以被繼續(xù)執(zhí)行而不會產(chǎn)生進一步的消息， 因此建議你在一個單獨的開發(fā)環(huán)境中進行測試。

根據(jù)需要設置 plpgsql.extra_warnings 或 plpgsql.extra_errors，適當情況下，在開發(fā)和/或測試環(huán)境中可以設置為 "all"。

這些附加檢查通過配置變量啟用， plpgsql.extra_warnings用于警告，plpgsql.extra_errors 用于錯誤。 兩者都可以設置為逗號分隔的檢查列表，"none" 或 "all"。 默認值為"none"。當前可用的檢查列表包括：

shadowed_variables

檢查聲明是否遮蓋了以前定義的變量

strict_multi_assignment

某些PL/PgSQL命令允許一次將值分配給多個變量，例如SELECT INTO。 通常，目標變量的數(shù)量和源變量的數(shù)量應匹配，盡管PL/PgSQL將使用NULL來處理缺失的值和被忽略的額外變量。 啟用此檢查將導致 PL/PgSQL在目標變量數(shù)和源變量數(shù)不同時引發(fā) WARNING或ERROR。

too_many_rows

Enabling this check will cause PL/PgSQL to check if a given query returns more than one row when an INTO clause is used. As an INTO statement will only ever use one row, having a query return multiple rows is generally either inefficient and/or nondeterministic and therefore is likely an error. 啟用此檢查將導致PL/PgSQL檢查在使用INTO子句時給定查詢是否返回多行。 由于INTO語句只會使用一行，讓查詢返回多行通常會效率低下和/或不確定性，因此很可能會出現(xiàn)錯誤。

下面的示例顯示了plpgsql.extra_warnings 設置為shadowed_variables的效果：

SET plpgsql.extra_warnings TO 'shadowed_variables';

CREATE FUNCTION foo(f1 int) RETURNS int AS $$
DECLARE
f1 int;
BEGIN
RETURN f1;
END;
$$ LANGUAGE plpgsql;
WARNING:  variable "f1" shadows a previously defined variable
LINE 3: f1 int;
        ^
CREATE FUNCTION

下面的示例顯示了將plpgsql.extra_warnings 設置為strict_multi_assignment：

SET plpgsql.extra_warnings TO 'strict_multi_assignment';

CREATE OR REPLACE FUNCTION public.foo()
 RETURNS void
 LANGUAGE plpgsql
AS $$
DECLARE
  x int;
  y int;
BEGIN
  SELECT 1 INTO x, y;
  SELECT 1, 2 INTO x, y;
  SELECT 1, 2, 3 INTO x, y;
END;
$$;

SELECT foo();
WARNING:  number of source and target fields in assignment does not match
DETAIL:  strict_multi_assignment check of extra_warnings is active.
HINT:  Make sure the query returns the exact list of columns.
WARNING:  number of source and target fields in assignment does not match
DETAIL:  strict_multi_assignment check of extra_warnings is active.
HINT:  Make sure the query returns the exact list of columns.

 foo 
-----
 
(1 row)


以上內容是否對您有幫助：
在線筆記
App下載
App下載

掃描二維碼

下載編程獅App

公眾號
微信公眾號

編程獅公眾號