PostgreSQL – Fixing Table Reference Errors in Functions

catalogsdatabase-designplpgsqlpostgresql

So I run this query:

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

…while working on code based off of this answer and I get this error message:

ERROR: relation "parts1" already exists

pgAdmin III doesn't show the table (and I obviously reloaded the left column pressing F5 after clicking on the correct items because it's of cascading reload effect). Just to cover my bases I run the following query:

DROP TABLE IF EXISTS parts1 RESTRICT;

…to which psql returns:

NOTICE: table "parts1" does not exist, skipping

So what is parts1 a reference to if not a table?

How many queries? All the queries:

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;

Best Answer

Your issue is that you have defined a recursive function ... the function is calling itself. The logic that exists in it currently would result in a stack dump with OUT OF MEMORY error (or something along those lines, at least) if it didn't give you the error of "table already exists" to begin with..

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;

Your order of SQL should be as such:

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

The function definition is what is between the dollar quoting..

Related Question