How would I loop through tables in a Postgres db to create materialized views for all non system tables?

Question

Below is the code I have been manually creating mviews with where the variables would be table_schema, table_name and [list of columns].

CREATE MATERIALIZED VIEW table_schema.table_name_bvw
AS
    WITH t0_ AS
        (SELECT [list of columns]
        FROM
            (SELECT  [list of columns] , Row_number() over (PARTITION BY objectid ORDER BY gdb_from_date DESC) rn_
            FROM     table_schema.table_name
            WHERE    (gdb_branch_id = 0)) a
WHERE rn_ = 1
AND gdb_is_delete = 0 )
    SELECT   [list of columns]
    FROM     t0_
WITH DATA;

CREATE UNIQUE INDEX IF NOT EXISTS table_name_bvw_uuid
    ON table_schema.table_name_bvw USING btree
    (globalid COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;

CREATE INDEX IF NOT EXISTS table_name_bvw_spat_idx
    ON table_schema.table_name_bvw USING gist
    (shape)
    TABLESPACE pg_default;
    
ALTER TABLE IF EXISTS table_schema.table_name_bvw
    OWNER TO owner;

I have attempted to create a loop but am not sure how to get the 3 variables to pull from the database. Should I create a table from this and loop through?

CREATE TABLE usa_master.fhltable as

SELECT table_catalog, table_schema, 
table_name, table_schema || '.' || table_name as tablen,
table_schema || '.' || table_name || '_bvw' as tablebvw,
table_name || '_bvw_uuid' as bud,
table_name || '_bvw_spat_idx' as bspat,
array_to_string(array_agg(column_name order by columns.ordinal_position),',') column_name
FROM information_schema.columns  
WHERE table_schema <> 'pg_catalog'   AND table_name NOT LIKE 'i%'
  AND table_schema = 'fhl'
  group by table_catalog,table_schema,table_name
 ;

I also want it to skip if a mview already exists.

Answer 1

Well I figured it out! Passing parameters from a table into a Create fuction and looping through all the records. Worked great.

    DO $$
DECLARE
    mviews CURSOR FOR
        SELECT tablebvw,column_name,tablen,bud,bspat FROM usa_master.fhltable;
    currentrow record;
BEGIN
    FOR currentrow in mviews
    LOOP
    EXECUTE
    'CREATE MATERIALIZED VIEW IF NOT EXISTS ' || currentrow.tablebvw || 
' AS
    WITH t0_ AS
        (SELECT  ' || currentrow.column_name || '
        FROM
            (SELECT ' || currentrow.column_name || ', Row_number() over (PARTITION BY objectid ORDER BY gdb_from_date DESC) rn_
            FROM ' || currentrow.tablen || '
            WHERE    (gdb_branch_id = 0)) a
WHERE rn_ = 1
AND gdb_is_delete = 0 )
    SELECT  ' || currentrow.column_name || '
    FROM     t0_
WITH DATA;

CREATE UNIQUE INDEX IF NOT EXISTS ' || currentrow.bud ||
    ' ON ' || currentrow.tablebvw ||' USING btree
    (globalid COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;

CREATE INDEX IF NOT EXISTS ' || currentrow.bspat || '
    ON ' || currentrow.tablebvw || ' USING gist
    (shape)
    TABLESPACE pg_default;
    
ALTER TABLE IF EXISTS ' || currentrow.tablebvw || '
    OWNER TO masterschema';
    END LOOP;
END$$;

The trick was using currentrow as the variable source.