postgres.git / summary / log / commit / refs

commit    1f705e68ee4fcff5787723bbee256139ce05e1b7
Author:   Amit Kapila <akapila@postgresql.org>
Date:     Wed Sep 11 04:25:38 2019 +0000
First released in: 13.0, 9.4.25, 9.5.20, 9.6.16, 10.11, 11.6, 12.0

    Doc: Update PL/pgSQL sample function in plpgsql.sgml.
    
    The example used to explain 'Looping Through Query Results' uses
    pseudo-materialized views.  Replace it with a more up-to-date example
    which does the same thing with actual materialized views, which have
    been available since PostgreSQL 9.3.
    
    In the passing, change '%' as format specifier instead of '%s' as is used
    in other examples in plpgsql.sgml.
    
    Reported-by: Ian Barwick
    Author: Ian Barwick
    Reviewed-by: Amit Kapila
    Backpatch-through: 9.4
    Discussion: https://postgr.es/m/9a70d393-7904-4918-c97c-649f6d114b6a@2ndquadrant.com


 doc/src/sgml/plpgsql.sgml | 28 ++++++++++++++++++----------
 1 file changed, 18 insertions(+), 10 deletions(-)

diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index e442d2aaea2..0453c78ee03 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -2386,21 +2386,29 @@ END LOOP <optional> <replaceable>label</replaceable> </optional>;
      resulting from the <replaceable>query</replaceable> and the loop body is
      executed for each row. Here is an example:
 <programlisting>
-CREATE FUNCTION cs_refresh_mviews() RETURNS integer AS $$
+CREATE FUNCTION refresh_mviews() RETURNS integer AS $$  
 DECLARE
     mviews RECORD;
 BEGIN
-    RAISE NOTICE 'Refreshing materialized views...';
-
-    FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key LOOP
+    RAISE NOTICE 'Refreshing all materialized views...';
+    FOR mviews IN
+       SELECT n.nspname AS mv_schema,
+              c.relname AS mv_name,
+              pg_catalog.pg_get_userbyid(c.relowner) AS owner
+         FROM pg_catalog.pg_class c
+    LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)
+        WHERE c.relkind = 'm'
+     ORDER BY 1
+    LOOP
 
-        -- Now "mviews" has one record from cs_materialized_views
+        -- Now "mviews" has one record with information about the materialized view
 
-        RAISE NOTICE 'Refreshing materialized view %s ...', quote_ident(mviews.mv_name);
-        EXECUTE 'TRUNCATE TABLE ' || quote_ident(mviews.mv_name);
-        EXECUTE 'INSERT INTO '
-                   || quote_ident(mviews.mv_name) || ' '
-                   || mviews.mv_query;
+        RAISE NOTICE 'Refreshing materialized view %.% (owner: %)...',
+                     quote_ident(mviews.mv_schema),
+                     quote_ident(mviews.mv_name),
+                     quote_ident(mviews.owner);
+        EXECUTE 'REFRESH MATERIALIZED VIEW ' || quote_ident(mviews.mv_schema)
+                      || '.' || quote_ident(mviews.mv_name);
     END LOOP;
 
     RAISE NOTICE 'Done refreshing materialized views.';


[parent: 3d12829ccb16]