doc: update PL/pgSQL sample loop function
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
Attachments:
plpgsql-mviews-sample-func-update.1.patchtext/x-patch; name=plpgsql-mviews-sample-func-update.1.patchDownload
commit d9e99b90fd0e572b4fd2461d7188a0197dee16df
Author: Ian Barwick <ian@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.';
Hi
čt 29. 8. 2019 v 5:03 odesílatel Ian Barwick <ian.barwick@2ndquadrant.com>
napsal:
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.
+1
Pavel
Show quoted text
Regards
Ian Barwick
--
Ian Barwick https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
On Thu, Aug 29, 2019 at 10:07 AM Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hi
čt 29. 8. 2019 v 5:03 odesílatel Ian Barwick <ian.barwick@2ndquadrant.com> napsal:
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.
+1
The current example shows the usage of looping in plpgsql, so as such
there is no correctness issue, but OTOH there is no harm in updating
the example as proposed by Ian Barwick. Does anyone else see any
problem with this idea? If we agree to proceed with this update, it
might be better to backpatch it for the sake of consistency though I
am not sure about that.
--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com
On Sun, Sep 1, 2019 at 9:09 AM Amit Kapila <amit.kapila16@gmail.com> wrote:
The current example shows the usage of looping in plpgsql, so as such
there is no correctness issue, but OTOH there is no harm in updating
the example as proposed by Ian Barwick. Does anyone else see any
problem with this idea? If we agree to proceed with this update, it
might be better to backpatch it for the sake of consistency though I
am not sure about that.
While checking the patch in back-branches, I noticed that it doesn't
get applied to 9.4 due to the way the example forms the string. I
have done the required changes for 9.4 as well and attached is the
result.
Ian, if possible, can you once check the patch for 9.4?
--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com
Attachments:
0001-Doc-Update-PL-pgSQL-sample-function-in-plpgsql.sgml.patchapplication/octet-stream; name=0001-Doc-Update-PL-pgSQL-sample-function-in-plpgsql.sgml.patchDownload
From 9ccbf87f76be6975d844343d1979644f44773e43 Mon Sep 17 00:00:00 2001
From: Amit Kapila <akapila@postgresql.org>
Date: Wed, 11 Sep 2019 10:25:49 +0530
Subject: [PATCH] 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 | 26 ++++++++++++++++++--------
1 file changed, 18 insertions(+), 8 deletions(-)
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index ae73630..3194173 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.';
--
1.8.3.1
0001-94-Doc-Update-PL-pgSQL-sample-function-in-plpgsql.sgml.patchapplication/octet-stream; name=0001-94-Doc-Update-PL-pgSQL-sample-function-in-plpgsql.sgml.patchDownload
From d533c8db27a3c1eb91d6fe473fc2be5d876a701e Mon Sep 17 00:00:00 2001
From: Amit Kapila <akapila@postgresql.org>
Date: Wed, 11 Sep 2019 09:55:38 +0530
Subject: [PATCH] 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 e442d2a..0453c78 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.';
--
1.8.3.1
st 11. 9. 2019 v 7:45 odesílatel Amit Kapila <amit.kapila16@gmail.com>
napsal:
On Sun, Sep 1, 2019 at 9:09 AM Amit Kapila <amit.kapila16@gmail.com>
wrote:The current example shows the usage of looping in plpgsql, so as such
there is no correctness issue, but OTOH there is no harm in updating
the example as proposed by Ian Barwick. Does anyone else see any
problem with this idea? If we agree to proceed with this update, it
might be better to backpatch it for the sake of consistency though I
am not sure about that.While checking the patch in back-branches, I noticed that it doesn't
get applied to 9.4 due to the way the example forms the string. I
have done the required changes for 9.4 as well and attached is the
result.
Is question if for this queries should not be used some from
information_schema instead direct access to pg_catalog.
But I looked now, and we don't see materialized views in information_schema
- what is probably bug.
Pavel
Show quoted text
Ian, if possible, can you once check the patch for 9.4?
--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com
On Wed, Sep 11, 2019 at 11:40 AM Pavel Stehule <pavel.stehule@gmail.com> wrote:
st 11. 9. 2019 v 7:45 odesílatel Amit Kapila <amit.kapila16@gmail.com> napsal:
On Sun, Sep 1, 2019 at 9:09 AM Amit Kapila <amit.kapila16@gmail.com> wrote:
The current example shows the usage of looping in plpgsql, so as such
there is no correctness issue, but OTOH there is no harm in updating
the example as proposed by Ian Barwick. Does anyone else see any
problem with this idea? If we agree to proceed with this update, it
might be better to backpatch it for the sake of consistency though I
am not sure about that.While checking the patch in back-branches, I noticed that it doesn't
get applied to 9.4 due to the way the example forms the string. I
have done the required changes for 9.4 as well and attached is the
result.Is question if for this queries should not be used some from information_schema instead direct access to pg_catalog.
But I looked now, and we don't see materialized views in information_schema - what is probably bug.
I think you got the answer of this on a related thread. Do you see
any other problems or have any concerns about this?
--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com
st 11. 9. 2019 v 11:51 odesílatel Amit Kapila <amit.kapila16@gmail.com>
napsal:
On Wed, Sep 11, 2019 at 11:40 AM Pavel Stehule <pavel.stehule@gmail.com>
wrote:st 11. 9. 2019 v 7:45 odesílatel Amit Kapila <amit.kapila16@gmail.com>
napsal:
On Sun, Sep 1, 2019 at 9:09 AM Amit Kapila <amit.kapila16@gmail.com>
wrote:
The current example shows the usage of looping in plpgsql, so as such
there is no correctness issue, but OTOH there is no harm in updating
the example as proposed by Ian Barwick. Does anyone else see any
problem with this idea? If we agree to proceed with this update, it
might be better to backpatch it for the sake of consistency though I
am not sure about that.While checking the patch in back-branches, I noticed that it doesn't
get applied to 9.4 due to the way the example forms the string. I
have done the required changes for 9.4 as well and attached is the
result.Is question if for this queries should not be used some from
information_schema instead direct access to pg_catalog.
But I looked now, and we don't see materialized views in
information_schema - what is probably bug.
I think you got the answer of this on a related thread. Do you see
any other problems or have any concerns about this?
no
Pavel
Show quoted text
--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com
On 2019/09/11 14:44, Amit Kapila wrote:
On Sun, Sep 1, 2019 at 9:09 AM Amit Kapila <amit.kapila16@gmail.com> wrote:
The current example shows the usage of looping in plpgsql, so as such
there is no correctness issue, but OTOH there is no harm in updating
the example as proposed by Ian Barwick. Does anyone else see any
problem with this idea? If we agree to proceed with this update, it
might be better to backpatch it for the sake of consistency though I
am not sure about that.While checking the patch in back-branches, I noticed that it doesn't
get applied to 9.4 due to the way the example forms the string. I
have done the required changes for 9.4 as well and attached is the
result.
Aha, I had it in my head that 9.4 was being deprecated soon and didn't
check that far back, but turns out it's around until Feb. 2020.
Ian, if possible, can you once check the patch for 9.4?
Looks good, thanks for catching that!
Regards
Ian Barwick
--
Ian Barwick https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services