1 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
5 >Control Structures</TITLE
8 CONTENT="Modular DocBook HTML Stylesheet Version 1.7"><LINK
10 HREF="mailto:pgsql-docs@postgresql.org"><LINK
12 TITLE="PostgreSQL 7.4.1 Documentation"
13 HREF="index.html"><LINK
15 TITLE="PL/pgSQL - SQL Procedural Language"
16 HREF="plpgsql.html"><LINK
18 TITLE="Basic Statements"
19 HREF="plpgsql-statements.html"><LINK
22 HREF="plpgsql-cursors.html"><LINK
25 HREF="stylesheet.css"><META
27 CONTENT="2003-12-22T03:48:47"></HEAD
33 SUMMARY="Header navigation table"
43 >PostgreSQL 7.4.1 Documentation</TH
51 HREF="plpgsql-statements.html"
73 > Procedural Language</TD
87 HREF="plpgsql-cursors.html"
101 NAME="PLPGSQL-CONTROL-STRUCTURES"
102 >37.7. Control Structures</A
105 > Control structures are probably the most useful (and
106 important) part of <SPAN
113 >'s control structures,
114 you can manipulate <SPAN
118 flexible and powerful way.
125 NAME="PLPGSQL-STATEMENTS-RETURNING"
126 >37.7.1. Returning From a Function</A
129 > There are two commands available that allow you to return data
160 > with an expression terminates the
161 function and returns the value of
165 > to the caller. This form
166 is to be used for <SPAN
173 > When returning a scalar type, any expression can be used. The
174 expression's result will be automatically cast into the
175 function's return type as described for assignments. To return a
176 composite (row) value, you must write a record or row variable
183 > The return value of a function cannot be left undefined. If
184 control reaches the end of the top-level block of the function
185 without hitting a <TT
188 > statement, a run-time
192 > If you have declared the function to
200 must still be specified; but in this case the expression following
204 > is optional and will be ignored if present.
228 > function is declared to return
236 to follow is slightly different. In that case, the individual
237 items to return are specified in <TT
241 commands, and then a final <TT
245 with no argument is used to indicate that the function has
246 finished executing. <TT
250 with both scalar and composite data types; in the latter case, an
254 > of results will be returned.
257 > Functions that use <TT
261 called in the following fashion:
264 CLASS="PROGRAMLISTING"
265 >SELECT * FROM some_func();</PRE
268 That is, the function is used as a table source in a <TT
278 > does not actually return from the
279 function; it simply saves away the value of the expression (or
280 record or row variable, as appropriate for the data type being
281 returned). Execution then continues with the next statement in
285 > function. As successive
289 > commands are executed, the result
290 set is built up. A final <TT
294 have no argument, causes control to exit the function.
303 > The current implementation of <TT
310 > stores the entire result set
311 before returning from the function, as discussed above. That
312 means that if a <SPAN
315 > function produces a
316 very large result set, performance may be poor: data will be
317 written to disk to avoid memory exhaustion, but the function
318 itself will not return until the entire result set has been
319 generated. A future version of <SPAN
323 allow users to define set-returning functions
324 that do not have this limitation. Currently, the point at
325 which data begins being written to disk is controlled by the
329 > configuration variable. Administrators
330 who have sufficient memory to store larger result sets in
331 memory should consider increasing this parameter.
342 NAME="PLPGSQL-CONDITIONALS"
343 >37.7.2. Conditionals</A
349 > statements let you execute commands based on
350 certain conditions. <SPAN
372 >IF ... THEN ... ELSE</TT
379 >IF ... THEN ... ELSE IF</TT
386 >IF ... THEN ... ELSIF ... THEN ... ELSE</TT
407 >boolean-expression</VAR
418 > statements are the simplest form of
422 >. The statements between
430 executed if the condition is true. Otherwise, they are
436 CLASS="PROGRAMLISTING"
437 >IF v_user_id <> 0 THEN
438 UPDATE users SET email = v_email WHERE user_id = v_user_id;
458 >boolean-expression</VAR
478 > by letting you specify an
479 alternative set of statements that should be executed if the
480 condition evaluates to false.
485 CLASS="PROGRAMLISTING"
486 >IF parentid IS NULL OR parentid = ''''
490 RETURN hp_true_filename(parentid) || ''/'' || fullname;
495 CLASS="PROGRAMLISTING"
496 >IF v_count > 0 THEN
497 INSERT INTO users_count (count) VALUES (v_count);
520 > statements can be nested, as in the
524 CLASS="PROGRAMLISTING"
525 >IF demo_row.sex = ''m'' THEN
526 pretty_sex := ''man'';
528 IF demo_row.sex = ''f'' THEN
529 pretty_sex := ''woman'';
535 > When you use this form, you are actually nesting an
539 > statement inside the
543 > part of an outer <TT
547 statement. Thus you need one <TT
551 statement for each nested <TT
554 > and one for the parent
558 >. This is workable but grows
559 tedious when there are many alternatives to be checked.
571 >IF-THEN-ELSIF-ELSE</TT
578 >boolean-expression</VAR
588 >boolean-expression</VAR
598 >boolean-expression</VAR
619 >IF-THEN-ELSIF-ELSE</TT
620 > provides a more convenient
621 method of checking many alternatives in one statement.
622 Formally it is equivalent to nested
625 >IF-THEN-ELSE-IF-THEN</TT
626 > commands, but only one
633 > Here is an example:
636 CLASS="PROGRAMLISTING"
639 ELSIF number > 0 THEN
640 result := ''positive'';
641 ELSIF number < 0 THEN
642 result := ''negative'';
644 -- hmm, the only other possibility is that number is null
656 NAME="PLPGSQL-CONTROL-STRUCTURES-LOOPS"
657 >37.7.3. Simple Loops</A
676 > statements, you can arrange for your
680 > function to repeat a series
713 > defines an unconditional loop that is repeated indefinitely
714 until terminated by an <TT
721 statement. The optional label can be used by <TT
725 nested loops to specify which level of nesting should be
760 the innermost loop is terminated and the
761 statement following <TT
769 must be the label of the current or some outer level of nested loop
770 or block. Then the named loop or block is terminated and control
771 continues with the statement after the loop's/block's corresponding
781 > is present, loop exit occurs only if the specified condition
782 is true, otherwise control passes to the statement after <TT
790 CLASS="PROGRAMLISTING"
800 EXIT WHEN count > 0; -- same result as previous example
805 IF stocks > 100000 THEN
806 EXIT; -- invalid; cannot use EXIT outside of LOOP
845 > statement repeats a
846 sequence of statements so long as the condition expression
847 evaluates to true. The condition is checked just before
848 each entry to the loop body.
853 CLASS="PROGRAMLISTING"
854 >WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
855 -- some computations here
858 WHILE NOT boolean_expression LOOP
859 -- some computations here
873 > (integer variant)</A
906 > creates a loop that iterates over a range of integer
911 > is automatically defined as type
915 > and exists only inside the loop. The two expressions giving
916 the lower and upper bound of the range are evaluated once when entering
917 the loop. The iteration step is normally 1, but is -1 when <TT
924 > Some examples of integer <TT
929 CLASS="PROGRAMLISTING"
931 -- some computations here
932 RAISE NOTICE ''i is %'', i;
935 FOR i IN REVERSE 10..1 LOOP
936 -- some computations here
941 > If the lower bound is greater than the upper bound (or less than,
945 > case), the loop body is not
946 executed at all. No error is raised.
955 NAME="PLPGSQL-RECORDS-ITERATING"
956 >37.7.4. Looping Through Query Results</A
959 > Using a different type of <TT
962 > loop, you can iterate through
963 the results of a query and manipulate that data
964 accordingly. The syntax is:
987 The record or row variable is successively assigned each row
988 resulting from the query (a <TT
991 > command) and the loop
992 body is executed for each row. Here is an example:
994 CLASS="PROGRAMLISTING"
995 >CREATE FUNCTION cs_refresh_mviews() RETURNS integer AS '
999 PERFORM cs_log(''Refreshing materialized views...'');
1001 FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key LOOP
1003 -- Now "mviews" has one record from cs_materialized_views
1005 PERFORM cs_log(''Refreshing materialized view '' || quote_ident(mviews.mv_name) || ''...'');
1006 EXECUTE ''TRUNCATE TABLE '' || quote_ident(mviews.mv_name);
1007 EXECUTE ''INSERT INTO '' || quote_ident(mviews.mv_name) || '' '' || mviews.mv_query;
1010 PERFORM cs_log(''Done refreshing materialized views.'');
1013 ' LANGUAGE plpgsql;</PRE
1016 If the loop is terminated by an <TT
1019 > statement, the last
1020 assigned row value is still accessible after the loop.
1026 > statement is another way to iterate over
1042 >text_expression</VAR
1050 This is like the previous form, except that the source
1054 > statement is specified as a string
1055 expression, which is evaluated and replanned on each entry to
1059 > loop. This allows the programmer to choose the speed of
1060 a preplanned query or the flexibility of a dynamic query, just
1076 > parser presently distinguishes the
1080 > loops (integer or query result) by checking
1081 whether the target variable mentioned just after <TT
1085 declared as a record or row variable. If not, it's presumed to be
1089 > loop. This can cause rather nonintuitive error
1090 messages when the true problem is, say, that one has
1091 misspelled the variable name after the <TT
1095 the complaint will be something like <TT
1097 >missing ".." at end of SQL
1110 SUMMARY="Footer navigation table"
1121 HREF="plpgsql-statements.html"
1139 HREF="plpgsql-cursors.html"
1149 >Basic Statements</TD
OSDN Git Service
