主页 / dba / 问题 / 91731
Accepted
John
Asked: 2015-02-12 12:02:24 +0800 CST

使用动态 SQL 重置串行 PK 的序列

  • 772

如何使用字符串和DECLARE变量的组合动态引用序列名称（如下所示）？下面的代码是正确的还是有另一种方法可以做到这一点？

出于性能原因，我的目标是在DO/BEGIN块内执行此操作，而且它会通过做一些有用的事情来帮助我理解 plpgsql，所以除非我真的必须这样做，否则我不会将它混入 PHP 中。

DO $$
 DECLARE PKEY VARCHAR;
BEGIN

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;

--SELECT setval('parts1_id_seq', (SELECT MAX(pkey) + 1 FROM parts));
SELECT setval('parts1_' || PKEY || '_seq', (SELECT MAX(pkey) + 1 FROM parts));

  END;
$$ LANGUAGE plpgsql;
postgresql plpgsql

2 个回答

  1. Best Answer

    一般建议

    你自己提到过，你刚刚开始使用 Postgres。然而，您正在立即处理极其高级的任务、处理系统目录并使用高级动态 SQL 进行操作以使事情自动化。

    虽然您的目标看起来很合理，但您仍然需要从基础开始。这里要解释的太多了。从优秀手册的（相关部分）开始。我在下面提供了几个深层链接。

    回答

    要获取主键中涉及的列的名称（和数据类型），请使用这个更简单的查询：

    SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type
FROM   pg_index i
JOIN   pg_attribute a ON a.attrelid = i.indrelid
                     AND a.attnum = ANY(i.indkey)
WHERE  i.indrelid = 'tbl'::regclass
AND    i.indisprimary;

    我更新了您的原始查询似乎来自的Postgres Wiki 页面。

    但是，这可以返回多行，而您只分配一个值。假设您已经确定我们正在处理一个serial类型（单列）主键。否则，请使用我之前的答案中列出的类似技术来确保。

    然后使用专用的系统信息功能pg_get_serial_sequence()来确定使用序列的名称，就像我之前的回答中演示的那样。
    根据文档：

    获取 a或列使用的序列serial的名称smallserialbigserial

    DO
$do$
BEGIN
   EXECUTE (
   SELECT format($$SELECT setval('%s'::regclass, max(%I)) FROM %s$$
                 , pg_get_serial_sequence(a.attrelid::regclass::text, a.attname)
                 , a.attname
                 , a.attrelid::regclass
                )
   FROM   pg_index i
   JOIN   pg_attribute a ON a.attrelid = i.indrelid
                        AND a.attnum = i.indkey[0]
   WHERE  i.indrelid = 'tbl'::regclass
   AND    i.indisprimary
   );
END
$do$ LANGUAGE plpgsql;

    这将构建并执行以下形式的查询：

    SELECT setval('tbl_tbl_id_seq'::regclass, max(tbl_id)) FROM tbl;

    解释/建议

    • 这是高级的东西，并不适合初学者。如果您不确切知道自己在做什么，那么弄乱系统目录可能很快就会失败。

    • 在 Postgres 和 plpgsql 中坚持使用合法的全小写标识符，让您的生活更轻松。但永远不要在动态 SQL 中依赖它，您还需要时刻防范 SQL 注入。

    • 大量format()使用方便安全地构建查询字符串。

    • SELECT在 plpgsql 函数中，如果不分配结果，则无法调用。你会PERFORM改用。手册中的详细信息。
      我完全删除了它，因为我将所有内容都简化为一个EXECUTE.

    • 由于整个操作只对单列主键有意义，我将JOIN条件简化为a.attnum = i.indkey[0]
      Note that pg_index.indkeyhas the special (internal) type int2vector。与 Postgres 数组不同，它的索引从0开始，而不是 1。

    • DO养成在 plpgsql 代码（包括语句）周围使用带有标记的美元引号的习惯。这允许像我在示例中那样嵌套简单的美元引号。细节：

    • 你只需要一个SELECT：

      SELECT setval('tbl_tbl_id_seq'::regclass, max(tbl_id)) FROM tbl;

      代替：

      SELECT setval('tbl_tbl_id_seq'::regclass, (SELECT max(tbl_id) FROM tbl));

    SQL Fiddle展示了一些东西。

    • 4

  2. 任何时候你有一个 SQL 语句，它本质上是动态的（可能在运行时改变），你需要在函数内部运行，你需要EXECUTE为它运行。

    请参阅第 40.5.4 节。执行动态命令

    您需要注意，以这种方式执行 SQL 可能容易受到SQL 注入攻击。

    你上面的行：

    SELECT setval('parts1_' || INTO PKEY || '_seq', (SELECT MAX(pkey) + 1 FROM parts));

    会变成（至少，我相信这是本意）

    EXECUTE 'SELECT setval(''parts1_' || PKEY || '_seq'', (SELECT MAX(' || pkey || ') + 1 FROM parts))';
    • 注意 ..''字符串中的双引号 ( ) 被视为文字单引号（如 \'）
    • 注意 2 .. 为了防止 SQL 注入，您可以执行格式化字符串（因为 Erwin Brandstetter 已经在另一篇文章中为您提供了几个示例）
    • 1

相关问题