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...
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;
);
Sophia analyzes international trade, startup ecosystems, retail transformation, and supply chain logistics for modern digital publications.
