How to use an array as a variable in Postgres?

I have this .sql script:

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

However I always have this error:

ERROR: cannot cast type integer to integer[] 

Also I have tried with the word array before with the same result:

user_list integer[] = array(...) 

1 Answer

You're trying to assign a row set to an array. You can use array_agg() instead.

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

That being said this IMHO doesn't really help you with creating your materialized views. Just inline your select

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

or create an active_users view

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

and use it instead

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