PostgreSQL 函式認為表引用存在時它不存在

所以我執行這個查詢：

SELECT f_copy_tbl('parts','parts1');

…在根據此答案處理程式碼時，我收到以下錯誤消息：

錯誤：關係“parts1”已經存在

pgAdmin III 沒有顯示表格（我顯然在點擊正確的項目後按 F5 重新載入了左列，因為它具有級聯重新載入效果）。只是為了覆蓋我的基礎，我執行以下查詢：

DROP TABLE IF EXISTS parts1 RESTRICT;

… psql 返回：

注意：表“parts1”不存在，正在跳過

那麼，如果不是表格，那麼參考是什麼*？*parts1

多少查詢？所有查詢：

CREATE OR REPLACE FUNCTION f_copy_tbl(_tbl regclass, _newtbl text)
RETURNS void AS
$func$
BEGIN

-- Copy table
EXECUTE format('CREATE TABLE %I (LIKE %s INCLUDING ALL);', _newtbl, _tbl);

-- Fix serial columns, if any
EXECUTE (
SELECT concat_ws(E'\n'
      , string_agg('CREATE SEQUENCE ' || seq, E';\n') || ';'
      , string_agg(format('ALTER SEQUENCE %s OWNED BY %I.%I;'
                         , seq, _newtbl, a.attname), E'\n')
      , 'ALTER TABLE ' || quote_ident(_newtbl)
      , string_agg(format($$ALTER %I SET DEFAULT nextval('%s'::regclass)$$
                                , a.attname, seq), E',\n')
      )
FROM   pg_attribute  a
JOIN   pg_attrdef    ad ON ad.adrelid = a.attrelid
                      AND ad.adnum   = a.attnum
    , quote_ident(_newtbl || '_' || a.attname || '_seq') AS seq  -- new seq name
WHERE  a.attrelid = _tbl
AND    a.attnum > 0
AND    NOT a.attisdropped
AND    a.atttypid = ANY ('{int,int8,int2}'::regtype[])
AND    ad.adsrc = 'nextval('''
        || (pg_get_serial_sequence (a.attrelid::regclass::text, a.attname))::regclass
        || '''::regclass)'
);

--Now populate `parts1` table with `parts` table's content...
SELECT f_copy_tbl('parts','parts1');
INSERT INTO parts1 SELECT * FROM parts;

--Now get primary key so we can fix the sequence...
--DECLARE PKEY VARCHAR;
--SELECT pg_attribute.attname INTO PKEY FROM pg_index, pg_class, pg_attribute WHERE pg_class.oid = 'parts1'::regclass AND indrelid = pg_class.oid AND pg_attribute.attrelid = pg_class.oid AND pg_attribute.attnum = any(pg_index.indkey) AND indisprimary;

--Now 
--SELECT setval('parts1_' || INTO PKEY || '_seq', (SELECT MAX(pkey) + 1 FROM parts));


/**********
--Now get highest number of primary key *AND* increment it...
--DECLARE PKEY_NUMBER INTEGER;
--SELECT (PKEY + 1) INTO PKEY_NUMBER FROM parts1 ORDER BY PKEY DESC LIMIT 1;

--Fix primary key's autoincrement...
--ALTER SEQUENCE parts1_id_seq RESTART WITH PKEY_NUMBER;
*********/

END
$func$ LANGUAGE plpgsql VOLATILE;

您的問題是您已經定義了一個遞歸函式……該函式正在呼叫自己。如果它沒有給你“表已經存在”的錯誤，那麼目前存在的邏輯將導致堆棧轉儲出現記憶體不足錯誤（或至少類似的東西）。

CREATE OR REPLACE FUNCTION f_copy_tbl(_tbl regclass, _newtbl text)
RETURNS void AS
$func$
BEGIN

--<snip stuff>

--Now populate `parts1` table with `parts` table's content...
SELECT f_copy_tbl('parts','parts1');  --- THIS IS THE OFFENDING LINE HERE .. REMOVE IT
INSERT INTO parts1 SELECT * FROM parts;

--<snip stuff>

END
$func$ LANGUAGE plpgsql VOLATILE;

您的 SQL 順序應該是這樣的：

CREATE OR REPLACE FUNCTION my_func(param1 VARCHAR, param2 VARCHAR) RETURNS VOID AS $$
DECLARE
 var1 VARCHAR;
BEGIN
 -- DO STUFFS HERE FOR FUNCTION
END;
$$ LANGUAGE plpgsql;
-- THE ABOVE DEFINES THE FUNCTION

-- THE BELOW EXECUTES THE FUNCTION
SELECT my_func('val1', 'val2'); -- THIS ONE LINE WILL EXECUTE ALL SQL DEFINED INSIDE THE FUNCTION

函式定義是美元報價之間的內容。

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