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; 
