PostgreSQL – Utilize Data from SELECT in BEGIN or Use WITH

postgresql

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;

Best Answer

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

Related Question