8000 Minor copy-editing for plpgsql chapter. · postgrespro/postgres_cluster@aefc08e · GitHub
[go: up one dir, main page]
Skip to content

Commit aefc08e

Browse files
tglsfdctglsfdc
committed
Minor copy-editing for plpgsql chapter.
1 parent dbf8259 commit aefc08e

File tree

1 file changed

+97
-58
lines changed

1 file changed

+97
-58
lines changed

‎doc/src/sgml/plpgsql.sgml

Lines changed: 97 additions & 58 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.26 2003/09/12 22:17:23 tgl Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/plpgsql.sgml,v 1.27 2003/09/23 19:58:50 tgl Exp $
33
-->
44

55
<chapter id="plpgsql">
@@ -156,7 +156,7 @@ END;
156156

157157
<para>
158158
That means that your client application must send each query to
159-
the database server, wait for it to process it, receive the
159+
the database server, wait for it to be processed, receive the
160160
results, do some computation, then send other queries to the
161161
server. All this incurs interprocess communication and may also
162162
incur network overhead if your client is on a different machine
@@ -652,7 +652,7 @@ user_id users.user_id%TYPE;
652652
an existing table or view, by using the
653653
<replaceable>table_name</replaceable><literal>%ROWTYPE</literal>
654654
notation; or it can be declared by giving a composite type's name.
655-
(Since every table has an associated datatype of the same name,
655+
(Since every table has an associated composite type of the same name,
656656
it actually does not matter in <productname>PostgreSQL</> whether you
657657
write <literal>%ROWTYPE</literal> or not. But the form with
658658
<literal>%ROWTYPE</literal> is more portable.)
@@ -916,12 +916,13 @@ tax := subtotal * 0.06;
916916
variable, or list of scalar variables. This is done by:
917917

918918
<synopsis>
919-
SELECT INTO <replaceable>target</replaceable> <replaceable>expressions</replaceable> FROM ...;
919+
SELECT INTO <replaceable>target</replaceable> <replaceable>select_expressions</replaceable> FROM ...;
920920
</synopsis>
921921

922922
where <replaceable>target</replaceable> can be a record variable, a row
923923
variable, or a comma-separated list of simple variables and
924-
record/row fields.
924+
record/row fields. The <replaceable>select_expressions</replaceable>
925+
and the remainder of the command are the same as in regular SQL.
925926
</para>
926927

927928
<para>
@@ -1085,9 +1086,11 @@ EXECUTE <replaceable class="command">command-string</replaceable>;
10851086
The results from <command>SELECT</command> commands are discarded
10861087
by <command>EXECUTE</command>, and <command>SELECT INTO</command>
10871088
is not currently supported within <command>EXECUTE</command>.
1088-
So, the only way to extract a result from a dynamically-created
1089-
<command>SELECT</command> is to use the <command>FOR-IN-EXECUTE</> form
1090-
described later.
1089+
There are two ways to extract a result from a dynamically-created
1090+
<command>SELECT</command>: one is to use the <command>FOR-IN-EXECUTE</>
1091+
loop form described in <xref linkend="plpgsql-records-iterating">,
1092+
and the other is to use a cursor with <command>OPEN-FOR-EXECUTE</>, as
1093+
described in <xref linkend="plpgsql-cursor-opening">.
10911094
</para>
10921095

10931096
<para>
@@ -1107,8 +1110,8 @@ EXECUTE ''UPDATE tbl SET ''
11071110
<function>quote_literal(<type>text</type>)</function>.<indexterm><primary>quote_ident</><secondary>use
11081111
in
11091112
PL/pgSQL</></indexterm><indexterm><primary>quote_literal</><secondary>use
1110-
in PL/pgSQL</></indexterm> Variables containing column and table
1111-
identifiers should be passed to function
1113+
in PL/pgSQL</></indexterm> For safety, variables containing column and
1114+
table identifiers should be passed to function
11121115
<function>quote_ident</function>. Variables containing values
11131116
that should be literal strings in the constructed command should
11141117
be passed to <function>quote_literal</function>. Both take the
@@ -1157,8 +1160,8 @@ END;
11571160

11581161
<para>
11591162
There are several ways to determine the effect of a command. The
1160-
first method is to use the <command>GET DIAGNOSTICS</command>,
1161-
which has the form:
1163+
first method is to use the <command>GET DIAGNOSTICS</command>
1164+
command, which has the form:
11621165

11631166
<synopsis>
11641167
GET DIAGNOSTICS <replaceable>variable</replaceable> = <replaceable>item</replaceable> <optional> , ... </optional> ;
@@ -1179,15 +1182,15 @@ GET DIAGNOSTICS <replaceable>variable</replaceable> = <replaceable>item</replace
11791182
<para>
11801183
An example:
11811184
<programlisting>
1182-
GET DIAGNOSTICS var_integer = ROW_COUNT;
1185+
GET DIAGNOSTICS integer_var = ROW_COUNT;
11831186
</programlisting>
11841187
</para>
11851188

11861189
<para>
1187-
The second method to determine the effects of a command is the
1188-
special variable named <literal>FOUND</literal> of
1190+
The second method to determine the effects of a command is to check the
1191+
special variable named <literal>FOUND</literal>, which is of
11891192
type <type>boolean</type>. <literal>FOUND</literal> starts out
1190-
false within each <application>PL/pgSQL</application> function.
1193+
false within each <application>PL/pgSQL</application> function call.
11911194
It is set by each of the following types of statements:
11921195
<itemizedlist>
11931196
<listitem>
@@ -1200,7 +1203,7 @@ GET DIAGNOSTICS var_integer = ROW_COUNT;
12001203
<listitem>
12011204
<para>
12021205
A <command>PERFORM</> statement sets <literal>FOUND</literal>
1203-
true if it produces (discards) a row, false if no row is
1206+
true if it produces (and discards) a row, false if no row is
12041207
produced.
12051208
</para>
12061209
</listitem>
@@ -1271,7 +1274,7 @@ RETURN <replaceable>expression</replaceable>;
12711274
<command>RETURN</command> with an expression terminates the
12721275
function and returns the value of
12731276
<replaceable>expression</replaceable> to the caller. This form
1274-
is to be used for <application>PL/pgSQL</> functions that does
1277+
is to be used for <application>PL/pgSQL</> functions that do
12751278
not return a set.
12761279
</para>
12771280

@@ -1287,11 +1290,14 @@ RETURN <replaceable>expression</replaceable>;
12871290
The return value of a function cannot be left undefined. If
12881291
control reaches the end of the top-level block of the function
12891292
without hitting a <command>RETURN</command> statement, a run-time
1290-
error will occur. Note that if you have declared the function to
1293+
error will occur.
1294+
</para>
1295+
1296+
<para>
1297+
If you have declared the function to
12911298
return <type>void</type>, a <command>RETURN</command> statement
1292-
must still be specified; the expression following
1293-
<command>RETURN</command> is, however, optional and will be ignored in
1294-
any case.
1299+
must still be specified; but in this case the expression following
1300+
<command>RETURN</command> is optional and will be ignored if present.
12951301
</para>
12961302
</sect3>
12971303

@@ -1308,9 +1314,9 @@ RETURN NEXT <replaceable>expression</replaceable>;
13081314
to follow is slightly different. In that case, the individual
13091315
items to return are specified in <command>RETURN NEXT</command>
13101316
commands, and then a final <command>RETURN</command> command
1311-
with no arguments is used to indicate that the function has
1317+
with no argument is used to indicate that the function has
13121318
finished executing. <command>RETURN NEXT</command> can be used
1313-
with both scalar and composite data types; in the later case, an
1319+
with both scalar and composite data types; in the latter case, an
13141320
entire <quote>table</quote> of results will be returned.
13151321
</para>
13161322

@@ -1347,7 +1353,7 @@ SELECT * FROM some_func();
13471353
written to disk to avoid memory exhaustion, but the function
13481354
itself will not return until the entire result set has been
13491355
generated. A future version of <application>PL/pgSQL</> may
1350-
allow users to allow users to define set-returning functions
1356+
allow users to define set-returning functions
13511357
that do not have this limitation. Currently, the point at
13521358
which data begins being written to disk is controlled by the
13531359
<varname>sort_mem</> configuration variable. Administrators
@@ -1585,19 +1591,19 @@ EXIT <optional> <replaceable>label</replaceable> </optional> <optional> WHEN <re
15851591
<programlisting>
15861592
LOOP
15871593
-- some computations
1588-
IF count > 0 THEN
1594+
IF count &gt; 0 THEN
15891595
EXIT; -- exit loop
15901596 527E
END IF;
15911597
END LOOP;
15921598

15931599
LOOP
15941600
-- some computations
1595-
EXIT WHEN count > 0;
1601+
EXIT WHEN count &gt; 0; -- same result as previous example
15961602
END LOOP;
15971603

15981604
BEGIN
15991605
-- some computations
1600-
IF stocks > 100000 THEN
1606+
IF stocks &gt; 100000 THEN
16011607
EXIT; -- invalid; cannot use EXIT outside of LOOP
16021608
END IF;
16031609
END;
@@ -1625,7 +1631,7 @@ END LOOP;
16251631
<para>
16261632
For example:
16271633
<programlisting>
1628-
WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
1634+
WHILE amount_owed &gt; 0 AND gift_certificate_balance &gt; 0 LOOP
16291635
-- some computations here
16301636
END LOOP;
16311637

@@ -1660,19 +1666,20 @@ END LOOP;
16601666
Some examples of integer <literal>FOR</> loops:
16611667
<programlisting>
16621668
FOR i IN 1..10 LOOP
1663-
-- some expressions here
1669+
-- some computations here
16641670
RAISE NOTICE ''i is %'', i;
16651671
END LOOP;
16661672

16671673
FOR i IN REVERSE 10..1 LOOP
1668-
-- some expressions here
1674+
-- some computations here
16691675
END LOOP;
16701676
</programlisting>
16711677
</para>
16721678

16731679
<para>
1674-
If the lower bound is greater than the upper bound, the loop body is not
1675-
executed at all, but no error is raised.
1680+
If the lower bound is greater than the upper bound (or less than,
1681+
in the <literal>REVERSE</> case), the loop body is not
1682+
executed at all. No error is raised.
16761683
</para>
16771684
</sect3>
16781685
</sect2> F438
@@ -1744,7 +1751,9 @@ END LOOP;
17441751
declared as a record or row variable. If not, it's presumed to be
17451752
an integer <literal>FOR</> loop. This can cause rather nonintuitive error
17461753
messages when the true problem is, say, that one has
1747-
misspelled the variable name after the <literal>FOR</>.
1754+
misspelled the variable name after the <literal>FOR</>. Typically
1755+
the complaint will be something like <literal>missing ".." at end of SQL
1756+
expression</>.
17481757
</para>
17491758
</note>
17501759
</sect2>
@@ -1766,7 +1775,7 @@ END LOOP;
17661775
rows. (However, <application>PL/pgSQL</> users do not normally need
17671776
to worry about that, since <literal>FOR</> loops automatically use a cursor
17681777
internally to avoid memory problems.) A more interesting usage is to
1769-
return a reference to a cursor that it has created, allowing the
1778+
return a reference to a cursor that a function has created, allowing the
17701779
caller to read the rows. This provides an efficient way to return
17711780
large row sets from functions.
17721781
</para>
@@ -1819,8 +1828,8 @@ DECLARE
18191828
Before a cursor can be used to retrieve rows, it must be
18201829
<firstterm>opened</>. (This is the equivalent action to the SQL
18211830
command <command>DECLARE CURSOR</>.) <application>PL/pgSQL</> has
1822-
three forms of the <command>OPEN</> statement, two of which use unbound cursor
1823-
variables and the other uses a bound cursor variable.
1831+
three forms of the <command>OPEN</> statement, two of which use unbound
1832+
cursor variables while the third uses a bound cursor variable.
18241833
</para>
18251834

18261835
<sect3>
@@ -1958,7 +1967,7 @@ CLOSE <replaceable>cursor</replaceable>;
19581967
</synopsis>
19591968

19601969
<para>
1961-
<command>CLOSE</command> closes the Portal underlying an open
1970+
<command>CLOSE</command> closes the portal underlying an open
19621971
cursor. This can be used to release resources earlier than end of
19631972
transaction, or to free up the cursor variable to be opened again.
19641973
</para>
@@ -1976,18 +1985,41 @@ CLOSE curs1;
19761985

19771986
<para>
19781987
<application>PL/pgSQL</> functions can return cursors to the
1979-
caller. This is used to return multiple rows or columns from
1980-
the function. To do this, the function opens the cursor and returns the
1981-
cursor name to the caller. The caller can then
1982-
fetch rows from the cursor. The cursor can
1983-
be closed by the caller, or it will be closed automatically
1988+
caller. This is useful to return multiple rows or columns,
1989+
especially with very large result sets. To do this, the function
1990+
opens the cursor and returns the cursor name to the caller (or simply
1991+
opens the cursor using a portal name specified by or otherwise known
1992+
to the caller). The caller can then fetch rows from the cursor. The
1993+
cursor can be closed by the caller, or it will be closed automatically
19841994
when the transaction closes.
19851995
</para>
19861996

19871997
<para>
1988-
The cursor name returned by the function can be specified by the
1989-
caller or automatically generated. The following example shows
1990-
how a cursor name can be supplied by the caller:
1998+
The portal name used for a cursor can be specified by the
1999+
programmer or automatically generated. To specify a portal name,
2000+
simply assign a string to the <type>refcursor</> variable before
2001+
opening it. The string value of the <type>refcursor</> variable
2002+
will be used by <command>OPEN</> as the name of the underlying portal.
2003+
However, if the <type>refcursor</> variable is NULL,
2004+
<command>OPEN</> automatically generates a name that does not
2005+
conflict with any existing portal, and assigns it to the
2006+
<type>refcursor</> variable.
2007+
</para>
2008+
2009+
<note>
2010+
<para>
2011+
A bound cursor variable is initialized to the string value
2012+
representing its name, so that the portal name is the same as
2013+
the cursor variable name, unless the programmer overrides it
2014+
by assignment before opening the cursor. But an unbound cursor
2015+
variable defaults to an initial value of NULL, so it will receive
2016+
an automatically-generated unique name, unless overridden.
2017+
</para>
2018+
</note>
2019+
2020+
<para>
2021+
The following example shows one way a cursor name can be supplied by
2022+
the caller:
19912023

19922024
<programlisting>
19932025
CREATE TABLE test (col text);
@@ -2128,8 +2160,8 @@ RAISE EXCEPTION ''Inexistent ID --> %'', user_id;
21282160
<para>
21292161
<application>PL/pgSQL</application> can be used to define trigger
21302162
procedures. A trigger procedure is created with the
2131-
<command>CREATE FUNCTION</> command as a function with no
2132-
arguments and a return type of <type>trigger</type>. Note that
2163+
<command>CREATE FUNCTION</> command, declaring it as a function with
2164+
no arguments and a return type of <type>trigger</type>. Note that
21332165
the function must be declared with no arguments even if it expects
21342166
to receive arguments specified in <command>CREATE TRIGGER</> ---
21352167
trigger arguments are passed via <varname>TG_ARGV</>, as described
@@ -2255,24 +2287,31 @@ RAISE EXCEPTION ''Inexistent ID --> %'', user_id;
22552287
<para>
22562288
A trigger function must return either null or a record/row value
22572289
having exactly the structure of the table the trigger was fired
2258-
for. The return value of a <literal>BEFORE</> or <literal>AFTER</> statement-level
2259-
trigger or an <literal>AFTER</> row-level trigger is ignored; it may as well
2260-
be null. However, any of these types of triggers can still
2261-
abort the entire trigger operation by raising an error.
2290+
for.
22622291
</para>
22632292

22642293
<para>
22652294
Row-level triggers fired <literal>BEFORE</> may return null to signal the
22662295
trigger manager to skip the rest of the operation for this row
22672296
(i.e., subsequent triggers are not fired, and the
2268-
<command>INSERT</>/<command>UPDATE</>/<command>DELETE</> does not occur for this row). If a nonnull
2297+
<command>INSERT</>/<command>UPDATE</>/<command>DELETE</> does not occur
2298+
for this row). If a nonnull
22692299
value is returned then the operation proceeds with that row value.
22702300
Returning a row value different from the original value
2271-
of <varname>NEW</> alters the row that will be inserted or updated. It is
2272-
possible to replace single values directly in <varname>NEW</> and return <varname>NEW</>,
2301+
of <varname>NEW</> alters the row that will be inserted or updated
2302+
(but has no direct effect in the <command>DELETE</> case).
2303+
To alter the row to be stored, it is possible to replace single values
2304+
directly in <varname>NEW</> and return the modified <varname>NEW</>,
22732305
or to build a complete new record/row to return.
22742306
</para>
22752307

2308+
<para>
2309+
The return value of a <literal>BEFORE</> or <literal>AFTER</>
2310+
statement-level trigger or an <literal>AFTER</> row-level trigger is
2311+
always ignored; it may as well be null. However, any of these types of
2312+
triggers can still abort the entire operation by raising an error.
2313+
</para>
2314+
22762315
<para>
22772316
<xref linkend="plpgsql-trigger-example"> shows an example of a
22782317
trigger procedure in <application>PL/pgSQL</application>.
@@ -2284,7 +2323,7 @@ RAISE EXCEPTION ''Inexistent ID --> %'', user_id;
22842323
<para>
22852324
This example trigger ensures that any time a row is inserted or updated
22862325
in the table, the current user name and time are stamped into the
2287-
row. And it ensures that an employee's name is given and that the
2326+
row. And it checks that an employee's name is given and that the
22882327
salary is a positive value.
22892328
</para>
22902329

@@ -2343,7 +2382,7 @@ CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
23432382
This section explains differences between
23442383
<productname>PostgreSQL</>'s <application>PL/pgSQL</application>
23452384
language and Oracle's <application>PL/SQL</application> language,
2346-
to help developers that port applications from Oracle to
2385+
to help developers who port applications from Oracle to
23472386
<productname>PostgreSQL</>.
23482387
</para>
23492388

@@ -2815,7 +2854,7 @@ END;
28152854

28162855
<para>
28172856
The <application>PL/pgSQL</> version of
2818-
<command>EXECUTE</command> works similar to the
2857+
<command>EXECUTE</command> works similarly to the
28192858
<application>PL/SQL</> version, but you have to remember to use
28202859
<function>quote_literal(text)</function> and
28212860
<function>quote_string(text)</function> as described in <xref

0 commit comments

Comments
 (0)
0