blob: 96901b7e461f17a57d10bbb98b6621d79a872f59 (
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
129
130
131
132
133
134
135
|
<!--
doc/src/sgml/ref/do.sgml
PostgreSQL documentation
-->
<refentry id="sql-do">
<indexterm zone="sql-do">
<primary>DO</primary>
</indexterm>
<indexterm zone="sql-do">
<primary>anonymous code blocks</primary>
</indexterm>
<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>
<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</type>. It is parsed and
executed a single time.
</para>
<para>
The optional <literal>LANGUAGE</literal> 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</command>.
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</literal>.
</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 EXTENSION</command>.
<literal>plpgsql</literal> is installed by default, but other languages are not.
</para>
<para>
The user must have <literal>USAGE</literal> 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>
<para>
If <command>DO</command> is executed in a transaction block, then the
procedure code cannot execute transaction control statements. Transaction
control statements are only allowed if <command>DO</command> is executed in
its own transaction.
</para>
</refsect1>
<refsect1 id="sql-do-examples">
<title>Examples</title>
<para>
Grant all privileges on all views in schema <literal>public</literal> to
role <literal>webuser</literal>:
<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>
|
