主页 / dba / 问题 / 91623
Accepted
John
Asked: 2015-02-11 10:54:13 +0800 CST

PostgreSQL 在 BEGIN 中使用来自 SELECT 的数据还是使用 WITH？

  • 772

我浏览了9.3 的文档，没有发现任何表明我可以或不能在SELECT查询中使用来自查询的数据的信息BEGIN。有可能还是我必须求助于某种WITH查询？

我试图将两个查询合并到一个BEGIN查询中，以找出在对数据库执行查询时如何更高效。我有另一个项目，这只兔子从我构建了一个快速复制表的工具的地方开始（这​​样我就可以测试我正在构建的其他数据库工具而不是原始工具）但是它不会复制主键。

第一个查询获取sequence表的名称：

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';

第二个查询复制了一个表，尽管它已经包含sequence从第一个查询中获取的名称：

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;
postgresql

1 个回答

  1. Best Answer

    您可能更适合在匿名块内执行整个操作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小提琴

    • 2

相关问题