我有这个 .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(...)
您正在尝试将行集分配给数组。您可以使用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
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;
);
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)