Creating Function in Redshift to Refresh All Materialized Views

user_771997 0
Viewed 307

I'm attempting to create a function that can be called as part of daily processing to refresh all of our materialized views. I'd like this to be dynamic, as new materialized views may be added after this function is defined. Here's what I've got:

CREATE OR REPLACE FUNCTION refresh_all_materialized_views(schema_arg varchar)
RETURNS INT VOLATILE AS $$
DECLARE
    row RECORD;
BEGIN
    RAISE NOTICE 'Refreshing materialized view in schema %', schema_arg;
    FOR row IN SELECT name from STV_MV_INFO where schema = schema_arg
LOOP
    RAISE NOTICE 'Refreshing %.%', schema_arg, r.name;
    EXECUTE 'REFRESH MATERIALIZED VIEW ' || schema_arg || '.' || r.name;
END LOOP;

    RETURN 1;
END;
$$ LANGUAGE sql;

Redshift is throwing an error stating:

[42601][500310] Amazon Invalid operation: syntax error at or near "RECORD" Position: 125;

I'm not sure what's incorrect. If "RECORD" isn't a valid type, how does dynamic SQL like this get implemented? Thanks for the time!

amazon-redshift sql-function sql
1 Answers

I was also looking for a solution to this problem, and now I have solved it, here is my method for your reference.

The official pointed out that user-defined-functions only supports select, not from. But he supports precompiled execution statements.

-- Create precompiled execution statements
PREPARE refresh_schema_mv (text)
AS select 'REFRESH MATERIALIZED VIEW '||schema||'.'||name||';' 
from stv_mv_info 
where schema = $1
and state <= 1
;

-- execute my statement
EXECUTE refresh_schema_mv ('dws');

-- When this statement does not need to be used again, it can be deleted
DEALLOCATE refresh_schema_mv;

-- Check my update status
select * from SVL_MV_REFRESH_STATUS
order by starttime desc;
user_197901890
Related