0

I went through the documentation for 9.3 and did not find anything suggesting that I can or can not use data from a SELECT query within a BEGIN query. Is it possible or will I have to resort to a WITH query of some kind?

I'm trying to merge two queries in to a single BEGIN query to figure out how to be more efficient when executing queries to the database. I have another project this rabbit trails from where I've built a tool that quickly duplicates a table (so I can test other database tools I'm building on instead of the originals) however it does not replicate the primary key.

This first query fetches the sequence name of a table:

SELECT s.relname AS sequence 
FROM pg_class AS s JOIN pg_depend d ON d.objid = s.oid 
INNER JOIN pg_class AS t ON d.objid = s.oid AND d.refobjid = t.oid 
INNER JOIN pg_attribute AS a ON (d.refobjid, d.refobjsubid) = (a.attrelid, a.attnum) 
INNER JOIN pg_namespace AS n ON n.oid = s.relnamespace 
WHERE s.relkind = 'S' 
AND n.nspname = 'public' 
AND t.relname='parts';

This second query duplicates a table though already contains the sequence name that is fetched from the first query:

BEGIN;
LOCK TABLE parts; 
CREATE TABLE parts1 (LIKE parts INCLUDING ALL);
INSERT INTO parts1 SELECT * FROM parts;
SELECT setval('parts_id_seq1', (SELECT max(id) FROM parts)+1);
COMMIT;
2

You would probably be better suited to do the whole operation inside an anonymous block.. DO ..

DO $$
  DECLARE
    SEQUENCE_NAME VARCHAR;
  BEGIN
    SELECT s.relname INTO SEQUENCE_NAME
    FROM pg_class AS s JOIN pg_depend d ON d.objid = s.oid 
    INNER JOIN pg_class AS t ON d.objid = s.oid AND d.refobjid = t.oid 
    INNER JOIN pg_attribute AS a ON (d.refobjid, d.refobjsubid) = (a.attrelid, a.attnum) 
    INNER JOIN pg_namespace AS n ON n.oid = s.relnamespace 
    WHERE s.relkind = 'S' 
    AND n.nspname = 'public' 
    AND t.relname='parts';

    LOCK TABLE parts;
    CREATE TABLE parts1 (LIKE parts INCLUDING ALL);
    INSERT INTO parts1 SELECT * FROM parts;
    -- change select to perform
    --SELECT setval(SEQUENCE_NAME::regclass, (SELECT max(id) FROM parts)+1);
    PERFORM setval(SEQUENCE_NAME::regclass, (SELECT max(id) FROM parts)+1);
  END;
$$ LANGUAGE plpgsql;

SQL Fiddle

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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