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

Commit1f705e6

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 parent3d12829 commit1f705e6

File tree

1 file changed

+18
-10
lines changed

1 file changed

+18
-10
lines changed

‎doc/src/sgml/plpgsql.sgml

Lines changed: 18 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -2386,21 +2386,29 @@ END LOOP <optional> <replaceable>label</replaceable> </optional>;
23862386
resulting from the <replaceable>query</replaceable> and the loop body is
23872387
executed for each row. Here is an example:
23882388
<programlisting>
2389-
CREATE FUNCTIONcs_refresh_mviews() RETURNS integer AS $$
2389+
CREATE FUNCTIONrefresh_mviews() RETURNS integer AS $$
23902390
DECLARE
23912391
mviews RECORD;
23922392
BEGIN
2393-
RAISE NOTICE 'Refreshing materialized views...';
2394-
2395-
FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key LOOP
2393+
RAISE NOTICE 'Refreshing all materialized views...';
2394+
FOR mviews IN
2395+
SELECT n.nspname AS mv_schema,
2396+
c.relname AS mv_name,
2397+
pg_catalog.pg_get_userbyid(c.relowner) AS owner
2398+
FROM pg_catalog.pg_class c
2399+
LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)
2400+
WHERE c.relkind = 'm'
2401+
ORDER BY 1
2402+
LOOP
23962403

2397-
-- Now "mviews" has one recordfrom cs_materialized_views
2404+
-- Now "mviews" has one recordwith information about the materialized view
23982405

2399-
RAISE NOTICE 'Refreshing materialized view %s ...', quote_ident(mviews.mv_name);
2400-
EXECUTE 'TRUNCATE TABLE ' || quote_ident(mviews.mv_name);
2401-
EXECUTE 'INSERT INTO '
2402-
|| quote_ident(mviews.mv_name) || ' '
2403-
|| mviews.mv_query;
2406+
RAISE NOTICE 'Refreshing materialized view %.% (owner: %)...',
2407+
quote_ident(mviews.mv_schema),
2408+
quote_ident(mviews.mv_name),
2409+
quote_ident(mviews.owner);
2410+
EXECUTE 'REFRESH MATERIALIZED VIEW ' || quote_ident(mviews.mv_schema)
2411+
|| '.' || quote_ident(mviews.mv_name);
24042412
END LOOP;
24052413

24062414
RAISE NOTICE 'Done refreshing materialized views.';

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp