postgresql - 如何在 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(...)

Answer 1

您正在尝试将一个行集分配给一个数组。您可以改用 array_agg()。

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

话虽这么说，恕我直言并不能真正帮助您创建物化 View 。只需内联您的选择

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 View

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