CODEWITHSUNDEEP
sqlpostgresqldynamic-sqlmaterialized-views
Jonathan Gerhartz
Jonathan Gerhartz

Reputation: 11

Execute Result of Dynamic Postgres Query

I have a table in my postgres db called rpt.view_refresh_schedule. This table contains the name of materialized views stored in my database that I would like to refresh on a schedule.

I have written the query below which takes each record in the rpt.view_refresh_schedule table and builds a dynamic query to refresh each table.

select CONCAT('REFRESH MATERIALIZED VIEW ' || view_name || ';') as script from rpt.view_refresh_schedule

The output of that query is as follows:

REFRESH MATERIALIZED VIEW mv_user_trading_activity;
REFRESH MATERIALIZED VIEW mv_orders;

I want to create a function that executes the dynamic queries that were produced as a result of my above sql. How can I do that? I have seen other posts which execute a query from a single string but this is multiple commands. In python I would just loop over the results and execute each one. Is there something similar in postgres? I have tried the LOOP function with no success.

Upvotes: 1

Views: 585

Answers (3)

Belayer
Belayer

Reputation: 14936

Unless your refresh schedule is truly dynamic meaning you update your table for a given materialized view often you do not need either function nor table. If you refresh interval is stable, for a given MV, you can define it on the create or alter materialized view. From the documentation section create_mv_refresh on create:

Use the create_mv_refresh clause to specify the default methods, modes, and times for the database to refresh the materialized view. If the master tables of a materialized view are modified, then the data in the materialized view must be updated to make the materialized view accurately reflect the data currently in its master tables. This clause lets you schedule the times and specify the method and mode for the database to refresh the materialized view.

Upvotes: 0

Jeremy
Jeremy

Reputation: 6723

If you are using psql, you can do this, without having to write a function:

select CONCAT('REFRESH MATERIALIZED VIEW ' || view_name || ';') as script from rpt.view_refresh_schedule
\gexec

Upvotes: 1

Mike Organek
Mike Organek

Reputation: 12494

Please give this a try:

DO $$
DECLARE 
  cmd text;
BEGIN
  FOR cmd in select CONCAT('REFRESH MATERIALIZED VIEW ' || view_name) as script 
               from rpt.view_refresh_schedule 
  LOOP
    EXECUTE cmd;
  END LOOP;
END;
$$;

Upvotes: 0

Related Questions