How to use an array as a variable in Postgres?

Question

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(...)

Answer 1

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