Skip to content

Commit 1b69b12

Browse files
tglsfdctglsfdc
committed
Some PL/PgSQL documentation improvements from Neil Conway.
1 parent c91b8bc commit 1b69b12

File tree

1 file changed

+111
-63
lines changed

1 file changed

+111
-63
lines changed

‎doc/src/sgml/plpgsql.sgml

Lines changed: 111 additions & 63 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/plpgsql.sgml,v 1.6 2002/09/01 16:28:05 tgl Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/plpgsql.sgml,v 1.7 2002/09/14 20:11:16 tgl Exp $
33
-->
44

55
<chapter id="plpgsql">
@@ -70,18 +70,19 @@ $Header: /cvsroot/pgsql/doc/src/sgml/plpgsql.sgml,v 1.6 2002/09/01 16:28:05 tgl
7070
</para>
7171
<para>
7272
As each expression and <acronym>SQL</acronym> query is first used
73-
in the function, the <application>PL/pgSQL</> interpreter creates a
74-
prepared execution plan (using the <acronym>SPI</acronym> manager's
75-
<function>SPI_prepare</function> and
76-
<function>SPI_saveplan</function> functions). Subsequent visits
77-
to that expression or query re-use the prepared plan. Thus, a function
78-
with conditional code that contains many statements for which execution
79-
plans might be required, will only prepare and save those plans
80-
that are really used during the lifetime of the database
81-
connection. This can provide a considerable savings of parsing
82-
activity. A disadvantage is that errors in a specific expression
83-
or query may not be detected until that part of the function is
84-
reached in execution.
73+
in the function, the <application>PL/pgSQL</> interpreter creates
74+
a prepared execution plan (using the <acronym>SPI</acronym>
75+
manager's <function>SPI_prepare</function> and
76+
<function>SPI_saveplan</function> functions). Subsequent visits
77+
to that expression or query re-use the prepared plan. Thus, a
78+
function with conditional code that contains many statements for
79+
which execution plans might be required will only prepare and save
80+
those plans that are really used during the lifetime of the
81+
database connection. This can substantially reduce the total
82+
amount of time required to parse, and generate query plans for the
83+
statements in a procedural language function. A disadvantage is
84+
that errors in a specific expression or query may not be detected
85+
until that part of the function is reached in execution.
8586
</para>
8687
<para>
8788
Once <application>PL/pgSQL</> has made a query plan for a particular
@@ -110,14 +111,26 @@ END;
110111
</para>
111112

112113
<para>
113-
Because <application>PL/pgSQL</application> saves execution plans in this way, queries that appear
114-
directly in a <application>PL/pgSQL</application> function must refer to the same tables and fields
115-
on every execution; that is, you cannot use a parameter as the name of
116-
a table or field in a query. To get around
117-
this restriction, you can construct dynamic queries using the <application>PL/pgSQL</application>
118-
EXECUTE statement --- at the price of constructing a new query plan
119-
on every execution.
114+
Because <application>PL/pgSQL</application> saves execution plans
115+
in this way, queries that appear directly in a
116+
<application>PL/pgSQL</application> function must refer to the
117+
same tables and fields on every execution; that is, you cannot use
118+
a parameter as the name of a table or field in a query. To get
119+
around this restriction, you can construct dynamic queries using
120+
the <application>PL/pgSQL</application> EXECUTE statement --- at
121+
the price of constructing a new query plan on every execution.
120122
</para>
123+
124+
<note>
125+
<para>
126+
The <application>PL/pgSQL</application> EXECUTE statement is not
127+
related to the EXECUTE statement supported by the
128+
<productname>PostgreSQL</productname> backend. The backend
129+
EXECUTE statement cannot be used within PL/PgSQL functions (and
130+
is not needed).
131+
</para>
132+
</note>
133+
121134
<para>
122135
Except for input/output conversion and calculation functions
123136
for user defined types, anything that can be defined in C language
@@ -152,11 +165,11 @@ END;
152165
<title>Better Performance</title>
153166

154167
<para>
155-
<acronym>SQL</acronym> is the language <productname>PostgreSQL</> (and
156-
most other Relational Databases) use as query
157-
language. It's portable and easy to learn. But every
158-
<acronym>SQL</acronym> statement must be executed
159-
individually by the database server.
168+
<acronym>SQL</acronym> is the language
169+
<productname>PostgreSQL</> (and most other relational databases)
170+
use as query language. It's portable and easy to learn. But
171+
every <acronym>SQL</acronym> statement must be executed
172+
individually by the database server.
160173
</para>
161174

162175
<para>
@@ -195,9 +208,10 @@ END;
195208
<title>Portability</title>
196209

197210
<para>
198-
Because <application>PL/pgSQL</application> functions run inside <productname>PostgreSQL</>, these
199-
functions will run on any platform where <productname>PostgreSQL</>
200-
runs. Thus you can reuse code and have less development costs.
211+
Because <application>PL/pgSQL</application> functions run inside
212+
<productname>PostgreSQL</>, these functions will run on any
213+
platform where <productname>PostgreSQL</> runs. Thus you can
214+
reuse code and reduce development costs.
201215
</para>
202216
</sect3>
203217
</sect2>
@@ -227,16 +241,17 @@ END;
227241
</para>
228242

229243
<para>
230-
One good way to develop in <application>PL/pgSQL</> is to simply use the text
231-
editor of your choice to create your functions, and in another
232-
console, use <command>psql</command> (PostgreSQL's interactive monitor) to load
233-
those functions. If you are doing it this way, it is a good
234-
idea to write the function using <command>CREATE OR REPLACE
235-
FUNCTION</command>. That way you can reload the file to update
236-
the function definition. For example:
244+
One good way to develop in <application>PL/pgSQL</> is to simply
245+
use the text editor of your choice to create your functions, and
246+
in another window, use <command>psql</command>
247+
(<productname>PostgreSQL</>'s interactive monitor) to load those
248+
functions. If you are doing it this way, it is a good idea to
249+
write the function using <command>CREATE OR REPLACE
250+
FUNCTION</>. That way you can reload the file to update the
251+
function definition. For example:
237252
<programlisting>
238253
CREATE OR REPLACE FUNCTION testfunc(INTEGER) RETURNS INTEGER AS '
239-
....
254+
....
240255
end;
241256
' LANGUAGE 'plpgsql';
242257
</programlisting>
@@ -645,9 +660,9 @@ RENAME this_var TO that_var;
645660

646661
<note>
647662
<para>
648-
RENAME appears to be broken as of PostgreSQL 7.2. Fixing this is
649-
of low priority, since ALIAS covers most of the practical uses of
650-
RENAME.
663+
RENAME appears to be broken as of <productname>PostgreSQL</>
664+
7.3. Fixing this is of low priority, since ALIAS covers most of
665+
the practical uses of RENAME.
651666
</para>
652667
</note>
653668

@@ -898,7 +913,7 @@ END;
898913
PERFORM <replaceable>query</replaceable>;
899914
</synopsis>
900915

901-
This executes a <literal>SELECT</literal>
916+
This executes a <command>SELECT</command>
902917
<replaceable>query</replaceable> and discards the
903918
result. <application>PL/pgSQL</application> variables are
904919
substituted in the query as usual. Also, the special variable
@@ -1044,6 +1059,10 @@ END;
10441059
<title>Obtaining result status</title>
10451060

10461061
<para>
1062+
There are several ways to determine the effect of a command. The
1063+
first method is to use the <literal>GET DIAGNOSTICS</literal>,
1064+
which has the form:
1065+
10471066
<synopsis>
10481067
GET DIAGNOSTICS <replaceable>variable</replaceable> = <replaceable>item</replaceable> <optional> , ... </optional> ;
10491068
</synopsis>
@@ -1166,10 +1185,19 @@ RETURN <replaceable>expression</replaceable>;
11661185
<para>
11671186
When a <application>PL/pgSQL</> function is declared to return
11681187
<literal>SETOF</literal> <replaceable>sometype</>, the procedure
1169-
to follow is slightly different. The individual items to be returned
1170-
are specified in RETURN NEXT commands, and then a final RETURN with
1171-
no argument is given to indicate that the function is done generating
1172-
items.
1188+
to follow is slightly different. In that case, the individual
1189+
items to return are specified in RETURN NEXT commands, and then a
1190+
final RETURN command with no arguments is used to indicate that
1191+
the function has finished executing. RETURN NEXT can be used with
1192+
both scalar and composite data types; in the later case, an
1193+
entire "table" of results will be returned. Functions that use
1194+
RETURN NEXT should be called in the following fashion:
1195+
1196+
<programlisting>
1197+
SELECT * FROM some_func();
1198+
</programlisting>
1199+
1200+
That is, the function is used as a table source in a FROM clause.
11731201

11741202
<synopsis>
11751203
RETURN NEXT <replaceable>expression</replaceable>;
@@ -1184,6 +1212,24 @@ RETURN NEXT <replaceable>expression</replaceable>;
11841212
RETURN, which need have no argument, causes control to exit
11851213
the function.
11861214
</para>
1215+
1216+
<note>
1217+
<para>
1218+
The current implementation of RETURN NEXT for PL/PgSQL stores
1219+
the entire result set before returning from the function, as
1220+
discussed above. That means that if a PL/PgSQL function
1221+
produces a very large result set, performance may be poor: data
1222+
will be written to disk to avoid memory exhaustion, but the
1223+
function itself will not return until the entire
1224+
result set has been generated. A future version of PL/PgSQL may
1225+
allow users to allow users to define set-returning functions
1226+
that do not have this limitation. Currently, the point at which
1227+
data begins being written to disk is controlled by the
1228+
<option>SORT_MEM</> configuration variable. Administrators who
1229+
have sufficient memory to store larger result sets in memory
1230+
should consider increasing this parameter.
1231+
</para>
1232+
</note>
11871233
</sect2>
11881234

11891235
<sect2 id="plpgsql-conditionals">
@@ -1904,13 +1950,14 @@ RAISE EXCEPTION ''Inexistent ID --> %'',user_id;
19041950
<title>Trigger Procedures</title>
19051951

19061952
<para>
1907-
<application>PL/pgSQL</application> can be used to define trigger
1908-
procedures. A trigger procedure is created with the <command>CREATE
1909-
FUNCTION</command> command as a function with no arguments and a return
1910-
type of <type>TRIGGER</type>. Note that the function must be declared
1911-
with no arguments even if it expects to receive arguments specified
1912-
in <command>CREATE TRIGGER</> --- trigger arguments are passed via
1913-
<varname>TG_ARGV</>, as described below.
1953+
<application>PL/pgSQL</application> can be used to define trigger
1954+
procedures. A trigger procedure is created with the
1955+
<command>CREATE FUNCTION</> command as a function with no
1956+
arguments and a return type of <type>TRIGGER</type>. Note that
1957+
the function must be declared with no arguments even if it expects
1958+
to receive arguments specified in <command>CREATE TRIGGER</> ---
1959+
trigger arguments are passed via <varname>TG_ARGV</>, as described
1960+
below.
19141961
</para>
19151962

19161963
<para>
@@ -2106,14 +2153,15 @@ CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
21062153
</para>
21072154

21082155
<para>
2109-
One painful detail in writing functions in <application>PL/pgSQL</application> is the handling
2110-
of single quotes. The function's source text in <command>CREATE FUNCTION</command> must
2111-
be a literal string. Single quotes inside of literal strings must be
2112-
either doubled or quoted with a backslash. We are still looking for
2113-
an elegant alternative. In the meantime, doubling the single quotes
2114-
as in the examples below should be used. Any solution for this
2115-
in future versions of <productname>PostgreSQL</productname> will be
2116-
forward compatible.
2156+
One painful detail in writing functions in
2157+
<application>PL/pgSQL</application> is the handling of single
2158+
quotes. The function's source text in <command>CREATE FUNCTION</>
2159+
must be a literal string. Single quotes inside of literal strings
2160+
must be either doubled or quoted with a backslash. We are still
2161+
looking for an elegant alternative. In the meantime, doubling the
2162+
single quotes as in the examples below should be used. Any
2163+
solution for this in future versions of
2164+
<productname>PostgreSQL</productname> will be forward compatible.
21172165
</para>
21182166

21192167
<para>
@@ -2504,7 +2552,7 @@ END;
25042552

25052553
<para>
25062554
The following procedure grabs rows from a
2507-
<literal>SELECT</literal> statement and builds a large function
2555+
<command>SELECT</command> statement and builds a large function
25082556
with the results in <literal>IF</literal> statements, for the
25092557
sake of efficiency. Notice particularly the differences in
25102558
cursors, <literal>FOR</literal> loops, and the need to escape
@@ -2735,7 +2783,7 @@ show errors
27352783

27362784
<callout arearefs="co.plpgsql-porting-locktable">
27372785
<para>
2738-
If you do a <literal>LOCK TABLE</literal> in <application>PL/pgSQL</>, the lock
2786+
If you do a <command>LOCK TABLE</command> in <application>PL/pgSQL</>, the lock
27392787
will not be released until the calling transaction is finished.
27402788
</para>
27412789
</callout>
@@ -2746,7 +2794,7 @@ show errors
27462794
entire function (and other functions called from therein) is
27472795
executed in a transaction and <productname>PostgreSQL</> rolls back the results if
27482796
something goes wrong. Therefore only one
2749-
<literal>BEGIN</literal> statement is allowed.
2797+
<command>BEGIN</command> statement is allowed.
27502798
</para>
27512799
</callout>
27522800

@@ -2895,7 +2943,7 @@ END;
28952943
<title>EXECUTE</title>
28962944

28972945
<para>
2898-
The <productname>PostgreSQL</> version of <literal>EXECUTE</literal> works
2946+
The <productname>PostgreSQL</> version of <command>EXECUTE</command> works
28992947
nicely, but you have to remember to use
29002948
<function>quote_literal(TEXT)</function> and
29012949
<function>quote_string(TEXT)</function> as described in <xref

0 commit comments

Comments
 (0)