blob: 5f43d1cf52c4912338591b7c86c9133054c0f7c2 (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
|
<!--
doc/src/sgml/ref/do.sgml
PostgreSQL documentation
-->
<refentry id="SQL-DO">
<refmeta>
<refentrytitle>DO</refentrytitle>
<manvolnum>7</manvolnum>
<refmiscinfo>SQL - Language Statements</refmiscinfo>
</refmeta>
<refnamediv>
<refname>DO</refname>
<refpurpose>execute an anonymous code block</refpurpose>
</refnamediv>
<indexterm zone="sql-do">
<primary>DO</primary>
</indexterm>
<indexterm zone="sql-do">
<primary>anonymous code blocks</primary>
</indexterm>
<refsynopsisdiv>
<synopsis>
DO [ LANGUAGE <replaceable class="PARAMETER">lang_name</replaceable> ] <replaceable class="PARAMETER">code</replaceable>
</synopsis>
</refsynopsisdiv>
<refsect1>
<title>Description</title>
<para>
<command>DO</command> executes an anonymous code block, or in other
words a transient anonymous function in a procedural language.
</para>
<para>
The code block is treated as though it were the body of a function
with no parameters, returning <type>void</>. It is parsed and
executed a single time.
</para>
<para>
The optional <literal>LANGUAGE</> clause can be written either
before or after the code block.
</para>
</refsect1>
<refsect1>
<title>Parameters</title>
<variablelist>
<varlistentry>
<term><replaceable class="PARAMETER">code</replaceable></term>
<listitem>
<para>
The procedural language code to be executed. This must be specified
as a string literal, just as in <command>CREATE FUNCTION</>.
Use of a dollar-quoted literal is recommended.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable class="PARAMETER">lang_name</replaceable></term>
<listitem>
<para>
The name of the procedural language the code is written in.
If omitted, the default is <literal>plpgsql</>.
</para>
</listitem>
</varlistentry>
</variablelist>
</refsect1>
<refsect1>
<title>Notes</title>
<para>
The procedural language to be used must already have been installed
into the current database by means of <command>CREATE LANGUAGE</>.
<literal>plpgsql</> is installed by default, but other languages are not.
</para>
<para>
The user must have <literal>USAGE</> privilege for the procedural
language, or must be a superuser if the language is untrusted.
This is the same privilege requirement as for creating a function
in the language.
</para>
</refsect1>
<refsect1 id="sql-do-examples">
<title id="sql-do-examples-title">Examples</title>
<para>
Grant all privileges on all views in schema <literal>public</> to
role <literal>webuser</>:
<programlisting>
DO $$DECLARE r record;
BEGIN
FOR r IN SELECT table_schema, table_name FROM information_schema.tables
WHERE table_type = 'VIEW' AND table_schema = 'public'
LOOP
EXECUTE 'GRANT ALL ON ' || quote_ident(r.table_schema) || '.' || quote_ident(r.table_name) || ' TO webuser';
END LOOP;
END$$;
</programlisting></para>
</refsect1>
<refsect1>
<title>Compatibility</title>
<para>
There is no <command>DO</command> statement in the SQL standard.
</para>
</refsect1>
<refsect1>
<title>See Also</title>
<simplelist type="inline">
<member><xref linkend="sql-createlanguage"></member>
</simplelist>
</refsect1>
</refentry>
|
