doc: update PL/pgSQL sample loop function

[Ian Barwick]

Hi

Here:

  
https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING

we have a sample PL/PgSQL function (dating from at least 7.2) demonstrating
query result loops, which refreshes some pseudo materialized views stored in
a user-defined table.

As we've had proper materialized views since 9.3, I thought it might
be nice to update this with a self-contained sample which can be used
as-is; see attached patch.

(As a side note the current sample function contains a couple of "%s"
placeholders which should be just "%"; a quick search of plpgsql.sgml
shows this is the only place they occur).

Will submit to the next commitfest.


Regards

Ian Barwick

--
 Ian Barwick                   https://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

commit d9e99b90fd0e572b4fd2461d7188a0197dee16df
Author: Ian Barwick <i...@2ndquadrant.com>
Date:   Thu Aug 29 11:49:23 2019 +0900

    doc: update PL/pgSQL sample function
    
    The sample PL/PgSQL function here:
    
      https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING
    
    dates from at least PostgreSQL 7.2 and updates pseudo-materialized views
    defined in a user table.
    
    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

diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index ae73630a48..3194173594 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -2437,19 +2437,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 format('TRUNCATE TABLE %I', mviews.mv_name);
-        EXECUTE format('INSERT INTO %I %s', 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 format('REFRESH MATERIALIZED VIEW %I.%I', mviews.mv_schema, mviews.mv_name);
     END LOOP;
 
     RAISE NOTICE 'Done refreshing materialized views.';