PostgreSQL中search_path函數注意事項

PostgreSQL 與大多數資料庫一樣，通過schema，邏輯上劃分對象的歸屬，如圖。

 

因為有了schema的概念，在訪問資料庫中的對象時，我們需要指定是哪個schema下面的。

例如schema_a.table。

為了方便使用者使用，PostgreSQL 還有一個用戶端的環境參數叫search_path，用來控制搜尋路徑的順序。

例如 search_path= '"$user",public' 表示優先搜尋與目前使用者名同名的schema，如果沒有則搜尋public schema。

例如 目前使用者為a, 在資料庫中有一張表是a.tbl還有一張表public.tbl。 那麼select * from tbl，訪問的是a.tbl。

函數編寫注意事項
我們在寫函數時，很容易忽略一點，就是搜尋路徑。

比如我寫了一個這樣的函數

sanity=> CREATE OR REPLACE FUNCTION "digoal"."getbusinessname"("ptypeno" varchar)
  RETURNS "pg_catalog"."varchar" AS $BODY$

        BEGIN

                RETURN (select typename from tbl_core_bus where typeno= ptypeno);

        END;

$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
CREATE FUNCTION

會有點亂哦，tbl_core_bus表到底是哪個schema下的呢？
與search_path有關，所以如果在search_path中沒有這個表時，訪問這個函數就會報錯。

因此在寫函數時務必注意，使用schema，避免這種問題。

上面的函數可以改成如下，在訪問對象的前面加上schema表示

CREATE OR REPLACE FUNCTION "digoal"."getbusinessname"("ptypeno" varchar)
  RETURNS "pg_catalog"."varchar" AS $BODY$

        BEGIN

                RETURN (select typename from digoal.tbl_core_bus where typeno= ptypeno);

        END;

$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
或者改成如下，設定函數的參數search_path

CREATE OR REPLACE FUNCTION "digoal"."getbusinessname"("ptypeno" varchar)
  RETURNS "pg_catalog"."varchar" AS $BODY$

        BEGIN

                RETURN (select typename from tbl_core_bus where typeno= ptypeno);

        END;

$BODY$
  LANGUAGE 'plpgsql' VOLATILE set search_path='digoal';

或者改成如下，在函數內使用set命令設定search_path

CREATE OR REPLACE FUNCTION "digoal"."getbusinessname"("ptypeno" varchar)
  RETURNS "pg_catalog"."varchar" AS $BODY$

        BEGIN
        set search_path='digoal';

                RETURN (select typename from tbl_core_bus where typeno= ptypeno);

        END;

$BODY$
  LANGUAGE 'plpgsql' VOLATILE ;