主页 / dba / 问题 / 91730
Accepted
John
Asked: 2015-02-12 11:41:41 +0800 CST

PostgreSQL 函数认为表引用存在时它不存在

  • 772

所以我运行这个查询：

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;
database-design postgresql

1 个回答

  1. Best Answer

    您的问题是您已经定义了一个递归函数......该函数正在调用自己。如果它没有给你“表已经存在”的错误，那么当前存在的逻辑将导致堆栈转储出现内存不足错误（或至少类似的东西）。

    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

    函数定义是美元报价之间的内容。

    • 3

相关问题