current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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;
share|improve this question
    
use these catalog queries to find out what may be in your system. stackoverflow.com/a/12455382/251174 –  swasheck Feb 11 at 19:54
    
@swasheck Thanks, I've got those commands in my MySQL/PostgreSQL comparison table when I started PostgreSQL back in November 2014. I know there is no table parts1 so now it's a matter of figuring out what the context of the problem is. –  John Feb 11 at 19:57
    
Are you possibly trying to create the table TWICE inside the function? –  Joishi Bodio Feb 11 at 20:03
    
Are you checking all objects or just tables?are you checking all schemas as well? –  swasheck Feb 11 at 20:05
    
@swasheck Updated post. –  John Feb 11 at 20:07

1 Answer 1

up vote 2 down vote accepted

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..

share|improve this answer
    
The SELECT f_copy_tbl line is what executes the function though. I disconnected and reconnected psql command line to make sure it wasn't stuck in a rut too. –  John Feb 11 at 20:20
1  
You are defining/declaring the function with the CREATE OR REPLACE FUNCTION statement. You call/execute the function LATER after it has already been defined... I will edit my answer to show you.. –  Joishi Bodio Feb 11 at 20:23
    
Ah, well how do I keep it all in a block "collective" of queries so I can execute everything in one single query instead of the traditional one-at-a-time query approach of execution that is inefficient? –  John Feb 11 at 20:31
    
I don't understand what you mean - one-at-a-time query is inefficient how? Are you intending to copy ALL tables in your db? The function you have defined will copy one table (based off the params you give to it).. If you need to execute queries together, you can either declare a function to bundle them together (as you have done), OR wrap them inside a transaction block (BEGIN .. COMMIT/ROLLBACK) –  Joishi Bodio Feb 11 at 20:38
    
@John: Joishi is right on target, the redundant, recursive call SELECT f_copy_tbl('parts','parts1'); inside the function is the problem. Create the (updated version of) the function I provided once (possible doing more). Then use it inside another function or plain SQL calls. Be aware that you cannot run plain SQL commands on the table parts1 before it exists. Depending on your exact method, Postgres may complain about that while planning queries. –  Erwin Brandstetter Feb 11 at 21:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.