如何使用 format() 函式引用限定的表名？

我在使用format下面範例中的函式正確引用表名時遇到了一些問題。

CREATE OR REPLACE FUNCTION copy_table(_source_tbl regclass, _target_tbl text)
RETURNS bool AS $func$
DECLARE query_str text; 
BEGIN
 query_str = format($fmt$ DROP TABLE IF EXISTS %1$I; CREATE TABLE %1$I AS (TABLE %s); $fmt$, _target_tbl, _source_tbl);
 EXECUTE query_str;
 RAISE NOTICE '%', query_str;
RETURN True;
END $func$ LANGUAGE plpgsql;

我的困境是我想引用輸入表名_target_tbl作為標識符（以避免 SQL 注入）。但是，給定一個完整的表名ex.test1，這會導致架構部分ex.被視為表名的一部分，並且public."ex.test1"在預設public.架構中創建的表如下所示。

我應該如何在這裡正確引用/格式化標識符？

=> SELECT copy_table('ex.test', 'ex.test1');
NOTICE:  table "ex.test1" does not exist, skipping
NOTICE:   DROP TABLE IF EXISTS "ex.test1"; CREATE TABLE "ex.test1" AS (TABLE ex.test);

=> \dt ex.test1
Did not find any relation named "ex.test1".
=> \dt "ex.test1"
        List of relations
Schema |   Name   | Type  | Owner 
--------+----------+-------+-------
public | ex.test1 | table | 
(1 row)

這是 PostgreSQL 10.3。

為避免目標表名不明確，請分別提供模式和表名。對於現有的表，Postgres 可以使用目前search_path預設到具有該名稱的對象的第一個模式來獲取不合格的表名。由於這對於（尚未）不存在的表顯然是不可能的，因此我們必須在那裡更加明確。

如果您通常希望將目標表與源表放在相同的模式中，您仍然可以省略模式名稱並將函式預設為（現有！）源表的模式以方便。像：

CREATE OR REPLACE FUNCTION copy_table(_source_tbl regclass
                                   , _target_tbl text
                                   , _target_schema text = NULL)
 RETURNS bool AS
$func$
DECLARE
  query_str text; 
BEGIN
  IF _target_schema IS NULL THEN               -- if no target schema provided ...
     SELECT c.relnamespace::regnamespace::text -- ... default to schema of input table
     FROM   pg_class c
     WHERE  c.oid = _source_tbl
     INTO  _target_schema;
  END IF;

  query_str = format('DROP TABLE IF EXISTS %1$I.%2$I;
                      CREATE TABLE %1$I.%2$I AS (TABLE %3$s);'
   , _target_schema
   , _target_tbl
   , _source_tbl);

  EXECUTE query_str;
  RAISE NOTICE '%', query_str;
  RETURN true;
END
$func$  LANGUAGE plpgsql;

稱呼：

SELECT copy_table('ex.test', 'text1', 'ex');

由於第三個（最後一個）參數_target_schema有一個預設值，我們可以省略它，在這種情況下，函式預設為源表的模式：

SELECT copy_table('ex.test', 'test1');

即使我們沒有明確提供源模式，也依賴於目前search_path：

SELECT copy_table('test', 'test1');

有關的：

• 表名作為 PostgreSQL 函式參數

您在這裡遇到了一個問題，您要在LIKE另一個表中重新創建一個表。我不明白為什麼這會是一個好主意。通常，當我看到這種東西時，它是在嘗試創建多租戶解決方案。我認為你走錯路了。作為一個完全不同的建議，請查看Row-Level Security。如果您需要確保使用者擁有主表的副本，最好只使用主表。

我也看不出對DROP IF EXISTS另一張桌子來說這是一個明智的想法。如果該表是臨時臨時表，您應該查看CREATE TEMPORARY TABLE- 然後您所要做的就是斷開連接，並在需要時重新創建它（CREATE TEMPORARY TABLE IF NOT EXISTS）。

無論如何，我只是在猜測。可以在另一個問題中提供更多細節以獲得更合適的答案。

引用自：https://dba.stackexchange.com/questions/200419