主页 / dba / 问题 / 222297
Accepted
Alexander Kleinhans
Asked: 2018-11-12 14:34:41 +0800 CST

如何将多个值选择到一个数组中并循环？（postgres 9.3）

  • 772

为了论证，我有一个简单的表格。我有一个选择 id 并循环遍历它们的函数，称为loop_test. 我可以选择一组 id 并循环遍历它们，从而导致我在事务中发生更改。

CREATE OR REPLACE FUNCTION loop_test() RETURNS void AS $$
DECLARE
        _ids_array INTEGER[];
        _id INTEGER;
BEGIN
        SELECT ARRAY(SELECT id FROM loop_test) INTO _ids_array; 
        FOREACH _id IN ARRAY _ids_array
        LOOP
                UPDATE loop_test SET looped = TRUE WHERE id = _id;
        END LOOP;
END;
$$ LANGUAGE plpgsql;

桌子：

db=# \d loop_test;
      Table "public.loop_test"
    Column     |  Type   | Modifiers 
---------------+---------+-----------
 id            | integer | 
 other_id      | integer | 
 id_copy       | integer | 
 other_id_copy | integer | 
 looped        | boolean | 

db=# select * from loop_test;
 id | other_id | id_copy | other_id_copy | looped 
----+----------+---------+---------------+--------
  1 |       10 |         |               | 
  6 |       15 |         |               | 
  2 |       11 |         |               | 
  7 |       16 |         |               | 
  3 |       12 |         |               | 
  4 |       13 |         |               | 
  5 |       14 |         |               | 
(7 rows)

当我打电话时select loop_test()，我得到以下结果：

db=# select * from loop_test;
 id | other_id | id_copy | other_id_copy | looped 
----+----------+---------+---------------+--------
  1 |       10 |         |               | t
  6 |       15 |         |               | t
  2 |       11 |         |               | t
  7 |       16 |         |               | t
  3 |       12 |         |               | t
  4 |       13 |         |               | t
  5 |       14 |         |               | t
(7 rows)

但是，我想创建一个函数来选择 theid和 theother_id到一个数组中。有人告诉我使用类似的东西agg_array，但我不完全理解它是如何工作的。

我在想像下面这样的事情？

CREATE OR REPLACE FUNCTION agg_loop_test() RETURNS void AS $$
DECLARE
        _ids_array INTEGER[][];
        _id INTEGER;
BEGIN
        SELECT AGG_ARRAY(SELECT id, other_id FROM loop_test) INTO _ids_array;
        FOREACH _id IN ARRAY _ids_array
        LOOP
                UPDATE loop_test SET id_copy = _id[0], other_id_copy = _id[1] WHERE id = _id[0];
        END LOOP;
END;
$$ LANGUAGE plpgsql;
postgresql aggregate

3 个回答

  1. Best Answer

    一个更好的方法，但是：只需更新。不需要循环。

    UPDATE loop_test
SET    id_copy = id
     , other_id_copy = other_id;
WHERE  id IS NOT NULL;

    该WHERE条件仅在id可以为 null 并且您想要与您所拥有的完全等价的情况下才有用。

    环形

    如果您只是在探索循环 - 您可以分配多个变量：

    CREATE OR REPLACE FUNCTION better_loop_test()
  RETURNS void AS
$func$
DECLARE
   _id int;
   _other_id int;
BEGIN
   -- example makes no sense, just a loop demo
   FOR _id, _other_id IN
      SELECT id, other_id FROM loop_test
   LOOP
      UPDATE loop_test
      SET    id_copy = _id
           , other_id_copy = _other_id
      WHERE id = _id;
   END LOOP;
END
$func$  LANGUAGE plpgsql;

    虽然您只需要已知类型的两列，但这可能比获取整个（可能很大）行便宜一些。

    • 6

  2. 我不了解多维数组，但我找到了一种更好的方法来做我想做的事情：

    CREATE OR REPLACE FUNCTION better_loop_test() RETURNS void AS $$
DECLARE
        _row RECORD;
BEGIN
        FOR _row IN SELECT * FROM loop_test LOOP
                UPDATE loop_test SET id_copy = _row.id, other_id_copy = _row.other_id WHERE id = _row.id;
        END LOOP;
END;
$$ LANGUAGE plpgsql;
    • 1

  3. @Erwin 的回复是绝对正确的。将数组用于描述的示例是性能错误（不幸的是，很常见）。有时它可能是必要的——因为您需要将一些值作为函数参数传递。

    有两种技术 - 1. 传递复合值数组，2. 传递多维数组。对我来说，性能应该是 +/- 相同的 - 在某些情况下，使用复合数组可能更具可读性。不确定，是否可以从 9.3 的查询结果创建多维数组。

    CREATE TYPE test_type AS (id1 int, id2 int);

CREATE OR REPLACE FUNCTION fx1(ids test_type[])
RETURNS void AS $$
DECLARE r test_type;
FOR r IN ARRAY ids
LOOP
  UPDATE ...
END LOOP;

    可能仍然只能使用一个UPDATE没有循环的语句 with function unnest：

    CREATE TABLE test (id1 integer, id2 integer);

UPDATE test SET id2 = u.id2 
  FROM unnest(array[(1,10),(3,4)]::test_type[]) u
 WHERE test.id1 = u.id1;

    性能影响取决于数组的大小——对于小数组来说，它是最小的——但仍然可能存在更深的循环嵌套，并且可能存在性能问题。

    对于多维数组PLpgSQLFOREACH语句有SLICE子句：

    CREATE OR REPLACE FUNCTION fx2(ids int[])
RETURNS void AS $$
DECLARE _ids int[];
BEGIN
  FOREACH _ids SLICE 1 IN ARRAY ids
  LOOP
    RAISE NOTICE 'ids[0]=% ids[1]=%', _ids[0], _ids[1];
  END LOOP;
END;
$$ LANGUAGE plpgsql;

postgres=# SELECT fx2(ARRAY[[1,2],[3,4]]);
NOTICE:  ids[0]=<NULL> ids[1]=1
NOTICE:  ids[0]=<NULL> ids[1]=3
    • 1

相关问题