summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/ref/do.sgml
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>