如何在Postgres中将数组用作变量？

Question

我有一个 .sql 脚本：

DO $$
  DECLARE 
    user_list integer[] = (select user_id from user where state = 'ACTIVE');
BEGIN
    CREATE CREATE MATERIALIZED VIEW accounts_with_active_users AS
    select * from accounts where user_account IN (user_list);
    ...

    CREATE CREATE MATERIALIZED VIEW accounts_without_active_users AS
    select * from accounts where user_account NOT IN (user_list);
    ...
END $$;

然而，我总是遇到这个错误：

ERROR: cannot cast type integer to integer[]

我之前尝试使用相同的单词 array，结果也是一样的：

user_list integer[] = array(...)

- AndreFontaine

Answer 1

您正在尝试将行集分配给数组。您可以使用 array_agg() 代替。

user_list integer[] = (select array_agg(user_id) from users where state = 'ACTIVE');

说实话，这并不能帮助你创建材料化视图。只需内联选择即可。

CREATE CREATE MATERIALIZED VIEW accounts_with_active_users AS
    SELECT * FROM accounts WHERE user_account IN (
        SELECT user_id FROM users WHERE state = 'ACTIVE'
    );

或者创建一个active_users视图

CREATE VIEW active_users AS SELECT * FROM users WHERE state = 'ACTIVE';

并使用它代替

CREATE CREATE MATERIALIZED VIEW accounts_with_active_users AS
    SELECT * FROM accounts WHERE user_account IN (
        SELECT user_id FROM active_users;
    );

- peterm

---

你可以使用 WHERE [NOT] EXISTS (SELECT 1 FROM users WHERE users.user_id = accounts.user_account AND user.state = 'ACTIVE') 来获得更好的计划。尝试一下，看看哪个更好。 - Laurenz Albe