Movatterモバイル変換


[0]ホーム

URL:


Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

Sign up
Appearance settings

Commit9b3c8f0

Browse files
author
Amit Kapila
committed
Doc: Update PL/pgSQL sample function in plpgsql.sgml.
The example used to explain 'Looping Through Query Results' usespseudo-materialized views. Replace it with a more up-to-date examplewhich does the same thing with actual materialized views, which havebeen available since PostgreSQL 9.3.In the passing, change '%' as format specifier instead of '%s' as is usedin other examples in plpgsql.sgml.Reported-by: Ian BarwickAuthor: Ian BarwickReviewed-by: Amit KapilaBackpatch-through: 9.4Discussion:https://postgr.es/m/9a70d393-7904-4918-c97c-649f6d114b6a@2ndquadrant.com
1 parentddbd5d8 commit9b3c8f0

File tree

1 file changed

+18
-8
lines changed

1 file changed

+18
-8
lines changed

‎doc/src/sgml/plpgsql.sgml

Lines changed: 18 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -2437,19 +2437,29 @@ END LOOP <optional> <replaceable>label</replaceable> </optional>;
24372437
resulting from the <replaceable>query</replaceable> and the loop body is
24382438
executed for each row. Here is an example:
24392439
<programlisting>
2440-
CREATE FUNCTIONcs_refresh_mviews() RETURNS integer AS $$
2440+
CREATE FUNCTIONrefresh_mviews() RETURNS integer AS $$
24412441
DECLARE
24422442
mviews RECORD;
24432443
BEGIN
2444-
RAISE NOTICE 'Refreshing materialized views...';
2445-
2446-
FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key LOOP
2444+
RAISE NOTICE 'Refreshing all materialized views...';
2445+
2446+
FOR mviews IN
2447+
SELECT n.nspname AS mv_schema,
2448+
c.relname AS mv_name,
2449+
pg_catalog.pg_get_userbyid(c.relowner) AS owner
2450+
FROM pg_catalog.pg_class c
2451+
LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)
2452+
WHERE c.relkind = 'm'
2453+
ORDER BY 1
2454+
LOOP
24472455

2448-
-- Now "mviews" has one recordfrom cs_materialized_views
2456+
-- Now "mviews" has one recordwith information about the materialized view
24492457

2450-
RAISE NOTICE 'Refreshing materialized view %s ...', quote_ident(mviews.mv_name);
2451-
EXECUTE format('TRUNCATE TABLE %I', mviews.mv_name);
2452-
EXECUTE format('INSERT INTO %I %s', mviews.mv_name, mviews.mv_query);
2458+
RAISE NOTICE 'Refreshing materialized view %.% (owner: %)...',
2459+
quote_ident(mviews.mv_schema),
2460+
quote_ident(mviews.mv_name),
2461+
quote_ident(mviews.owner);
2462+
EXECUTE format('REFRESH MATERIALIZED VIEW %I.%I', mviews.mv_schema, mviews.mv_name);
24532463
END LOOP;
24542464

24552465
RAISE NOTICE 'Done refreshing materialized views.';

0 commit comments

Comments
 (0)

[8]ページ先頭

©2009-2025 Movatter.jp