Movatterモバイル変換

RETURNexpression;

RETURN with an expression terminates the function and returns the value ofexpression to the caller. This form is used forPL/pgSQL functions that do not return a set.

In a function that returns a scalar type, the expression's result will automatically be cast into the function's return type as described for assignments. But to return a composite (row) value, you must write an expression delivering exactly the requested column set. This may require use of explicit casting.

If you declared the function with output parameters, write justRETURN with no expression. The current values of the output parameter variables will be returned.

If you declared the function to returnvoid, aRETURN statement can be used to exit the function early; but do not write an expression followingRETURN.

The return value of a function cannot be left undefined. If control reaches the end of the top-level block of the function without hitting aRETURN statement, a run-time error will occur. This restriction does not apply to functions with output parameters and functions returningvoid, however. In those cases aRETURN statement is automatically executed if the top-level block finishes.

Some examples:

-- functions returning a scalar typeRETURN 1 + 2;RETURN scalar_var;-- functions returning a composite typeRETURN composite_type_var;RETURN (1, 2, 'three'::text);  -- must cast columns to correct types

RETURN NEXTexpression;RETURN QUERYquery;RETURN QUERY EXECUTEcommand-string [ USINGexpression [, ...]];

When aPL/pgSQL function is declared to returnSETOFsometype, the procedure to follow is slightly different. In that case, the individual items to return are specified by a sequence ofRETURN NEXT orRETURN QUERY commands, and then a finalRETURN command with no argument is used to indicate that the function has finished executing.RETURN NEXT can be used with both scalar and composite data types; with a composite result type, an entire“table” of results will be returned.RETURN QUERY appends the results of executing a query to the function's result set.RETURN NEXT andRETURN QUERY can be freely intermixed in a single set-returning function, in which case their results will be concatenated.

RETURN NEXT andRETURN QUERY do not actually return from the function — they simply append zero or more rows to the function's result set. Execution then continues with the next statement in thePL/pgSQL function. As successiveRETURN NEXT orRETURN QUERY commands are executed, the result set is built up. A finalRETURN, which should have no argument, causes control to exit the function (or you can just let control reach the end of the function).

RETURN QUERY has a variantRETURN QUERY EXECUTE, which specifies the query to be executed dynamically. Parameter expressions can be inserted into the computed query string viaUSING, in just the same way as in theEXECUTE command.

If you declared the function with output parameters, write justRETURN NEXT with no expression. On each execution, the current values of the output parameter variable(s) will be saved for eventual return as a row of the result. Note that you must declare the function as returningSETOF record when there are multiple output parameters, orSETOFsometype when there is just one output parameter of typesometype, in order to create a set-returning function with output parameters.

Here is an example of a function usingRETURN NEXT:

CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT);INSERT INTO foo VALUES (1, 2, 'three');INSERT INTO foo VALUES (4, 5, 'six');CREATE OR REPLACE FUNCTION get_all_foo() RETURNS SETOF foo AS$BODY$DECLARE    r foo%rowtype;BEGIN    FOR r IN        SELECT * FROM foo WHERE fooid > 0    LOOP        -- can do some processing here        RETURN NEXT r; -- return current row of SELECT    END LOOP;    RETURN;END;$BODY$LANGUAGE plpgsql;SELECT * FROM get_all_foo();

Here is an example of a function usingRETURN QUERY:

CREATE FUNCTION get_available_flightid(date) RETURNS SETOF integer AS$BODY$BEGIN    RETURN QUERY SELECT flightid                   FROM flight                  WHERE flightdate >= $1                    AND flightdate < ($1 + 1);    -- Since execution is not finished, we can check whether rows were returned    -- and raise exception if not.    IF NOT FOUND THEN        RAISE EXCEPTION 'No flight at %.', $1;    END IF;    RETURN; END;$BODY$LANGUAGE plpgsql;-- Returns available flights or raises exception if there are no-- available flights.SELECT * FROM get_available_flightid(CURRENT_DATE);

• IF ... THEN ... END IF

• IF ... THEN ... ELSE ... END IF

• IF ... THEN ... ELSIF ... THEN ... ELSE ... END IF

• CASE ... WHEN ... THEN ... ELSE ... END CASE

• CASE WHEN ... THEN ... ELSE ... END CASE

IFboolean-expression THENstatementsEND IF;

IF-THEN statements are the simplest form ofIF. The statements betweenTHEN andEND IF will be executed if the condition is true. Otherwise, they are skipped.

Example:

IF v_user_id <> 0 THEN    UPDATE users SET email = v_email WHERE user_id = v_user_id;END IF;

IFboolean-expression THENstatementsELSEstatementsEND IF;

IF-THEN-ELSE statements add toIF-THEN by letting you specify an alternative set of statements that should be executed if the condition is not true. (Note this includes the case where the condition evaluates to NULL.)

Examples:

IF parentid IS NULL OR parentid = ''THEN    RETURN fullname;ELSE    RETURN hp_true_filename(parentid) || '/' || fullname;END IF;

IF v_count > 0 THEN    INSERT INTO users_count (count) VALUES (v_count);    RETURN 't';ELSE    RETURN 'f';END IF;

IFboolean-expression THENstatements[ ELSIFboolean-expression THENstatements[ ELSIFboolean-expression THENstatements    ...]][ ELSEstatements]END IF;

Sometimes there are more than just two alternatives.IF-THEN-ELSIF provides a convenient method of checking several alternatives in turn. TheIF conditions are tested successively until the first one that is true is found. Then the associated statement(s) are executed, after which control passes to the next statement afterEND IF. (Any subsequentIF conditions arenot tested.) If none of theIF conditions is true, then theELSE block (if any) is executed.

Here is an example:

IF number = 0 THEN    result := 'zero';ELSIF number > 0 THEN    result := 'positive';ELSIF number < 0 THEN    result := 'negative';ELSE    -- hmm, the only other possibility is that number is null    result := 'NULL';END IF;

The key wordELSIF can also be spelledELSEIF.

An alternative way of accomplishing the same task is to nestIF-THEN-ELSE statements, as in the following example:

IF demo_row.sex = 'm' THEN    pretty_sex := 'man';ELSE    IF demo_row.sex = 'f' THEN        pretty_sex := 'woman';    END IF;END IF;

However, this method requires writing a matchingEND IF for eachIF, so it is much more cumbersome than usingELSIF when there are many alternatives.

CASEsearch-expression    WHENexpression [,expression [ ...]] THENstatements  [ WHENexpression [,expression [ ...]] THENstatements    ...]  [ ELSEstatements]END CASE;

The simple form ofCASE provides conditional execution based on equality of operands. Thesearch-expression is evaluated (once) and successively compared to eachexpression in theWHEN clauses. If a match is found, then the correspondingstatements are executed, and then control passes to the next statement afterEND CASE. (SubsequentWHEN expressions are not evaluated.) If no match is found, theELSEstatements are executed; but ifELSE is not present, then aCASE_NOT_FOUND exception is raised.

Here is a simple example:

CASE x    WHEN 1, 2 THEN        msg := 'one or two';    ELSE        msg := 'other value than one or two';END CASE;

CASE    WHENboolean-expression THENstatements  [ WHENboolean-expression THENstatements    ...]  [ ELSEstatements]END CASE;

The searched form ofCASE provides conditional execution based on truth of Boolean expressions. EachWHEN clause'sboolean-expression is evaluated in turn, until one is found that yieldstrue. Then the correspondingstatements are executed, and then control passes to the next statement afterEND CASE. (SubsequentWHEN expressions are not evaluated.) If no true result is found, theELSEstatements are executed; but ifELSE is not present, then aCASE_NOT_FOUND exception is raised.

Here is an example:

CASE    WHEN x BETWEEN 0 AND 10 THEN        msg := 'value is between zero and ten';    WHEN x BETWEEN 11 AND 20 THEN        msg := 'value is between eleven and twenty';END CASE;

This form ofCASE is entirely equivalent toIF-THEN-ELSIF, except for the rule that reaching an omittedELSE clause results in an error rather than doing nothing.

[ <<label>>]LOOPstatementsEND LOOP [label];

LOOP defines an unconditional loop that is repeated indefinitely until terminated by anEXIT orRETURN statement. The optionallabel can be used byEXIT andCONTINUE statements within nested loops to specify which loop those statements refer to.

EXIT [label] [ WHENboolean-expression];

If nolabel is given, the innermost loop is terminated and the statement followingEND LOOP is executed next. Iflabel is given, it must be the label of the current or some outer level of nested loop or block. Then the named loop or block is terminated and control continues with the statement after the loop's/block's correspondingEND.

IfWHEN is specified, the loop exit occurs only ifboolean-expression is true. Otherwise, control passes to the statement afterEXIT.

EXIT can be used with all types of loops; it is not limited to use with unconditional loops.

When used with aBEGIN block,EXIT passes control to the next statement after the end of the block. Note that a label must be used for this purpose; an unlabeledEXIT is never considered to match aBEGIN block. (This is a change from pre-8.4 releases ofPostgreSQL, which would allow an unlabeledEXIT to match aBEGIN block.)

Examples:

LOOP    -- some computations    IF count > 0 THEN        EXIT;  -- exit loop    END IF;END LOOP;LOOP    -- some computations    EXIT WHEN count > 0;  -- same result as previous exampleEND LOOP;<<ablock>>BEGIN    -- some computations    IF stocks > 100000 THEN        EXIT ablock;  -- causes exit from the BEGIN block    END IF;    -- computations here will be skipped when stocks > 100000END;

CONTINUE [label] [ WHENboolean-expression];

If nolabel is given, the next iteration of the innermost loop is begun. That is, all statements remaining in the loop body are skipped, and control returns to the loop control expression (if any) to determine whether another loop iteration is needed. Iflabel is present, it specifies the label of the loop whose execution will be continued.

IfWHEN is specified, the next iteration of the loop is begun only ifboolean-expression is true. Otherwise, control passes to the statement afterCONTINUE.

CONTINUE can be used with all types of loops; it is not limited to use with unconditional loops.

Examples:

LOOP    -- some computations    EXIT WHEN count > 100;    CONTINUE WHEN count < 50;    -- some computations for count IN [50 .. 100]END LOOP;

[ <<label>>]WHILEboolean-expression LOOPstatementsEND LOOP [label];

TheWHILE statement repeats a sequence of statements so long as theboolean-expression evaluates to true. The expression is checked just before each entry to the loop body.

For example:

WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP    -- some computations hereEND LOOP;WHILE NOT done LOOP    -- some computations hereEND LOOP;

[ <<label>>]FORname IN [ REVERSE]expression ..expression [ BYexpression] LOOPstatementsEND LOOP [label];

This form ofFOR creates a loop that iterates over a range of integer values. The variablename is automatically defined as typeinteger and exists only inside the loop (any existing definition of the variable name is ignored within the loop). The two expressions giving the lower and upper bound of the range are evaluated once when entering the loop. If theBY clause isn't specified the iteration step is 1, otherwise it's the value specified in theBY clause, which again is evaluated once on loop entry. IfREVERSE is specified then the step value is subtracted, rather than added, after each iteration.

Some examples of integerFOR loops:

FOR i IN 1..10 LOOP    -- i will take on the values 1,2,3,4,5,6,7,8,9,10 within the loopEND LOOP;FOR i IN REVERSE 10..1 LOOP    -- i will take on the values 10,9,8,7,6,5,4,3,2,1 within the loopEND LOOP;FOR i IN REVERSE 10..1 BY 2 LOOP    -- i will take on the values 10,8,6,4,2 within the loopEND LOOP;

If the lower bound is greater than the upper bound (or less than, in theREVERSE case), the loop body is not executed at all. No error is raised.

If alabel is attached to theFOR loop then the integer loop variable can be referenced with a qualified name, using thatlabel.

Tip

A block containing anEXCEPTION clause is significantly more expensive to enter and exit than a block without one. Therefore, don't useEXCEPTION without need.

Example 41.2. Exceptions withUPDATE/INSERT

CREATE TABLE db (a INT PRIMARY KEY, b TEXT);CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS$$BEGIN    LOOP        -- first try to update the key        UPDATE db SET b = data WHERE a = key;        IF found THEN            RETURN;        END IF;        -- not there, so try to insert the key        -- if someone else inserts the same key concurrently,        -- we could get a unique-key failure        BEGIN            INSERT INTO db(a,b) VALUES (key, data);            RETURN;        EXCEPTION WHEN unique_violation THEN            -- Do nothing, and loop to try the UPDATE again.        END;    END LOOP;END;$$LANGUAGE plpgsql;SELECT merge_db(1, 'david');SELECT merge_db(1, 'dennis');

Exception handlers frequently need to identify the specific error that occurred. There are two ways to get information about the current exception inPL/pgSQL: special variables and theGET STACKED DIAGNOSTICS command.

Within an exception handler, the special variableSQLSTATE contains the error code that corresponds to the exception that was raised (refer toTable A.1 for a list of possible error codes). The special variableSQLERRM contains the error message associated with the exception. These variables are undefined outside exception handlers.

Within an exception handler, one may also retrieve information about the current exception by using theGET STACKED DIAGNOSTICS command, which has the form:

GET STACKED DIAGNOSTICSvariable { = | := }item [ , ...];

Eachitem is a key word identifying a status value to be assigned to the specifiedvariable (which should be of the right data type to receive it). The currently available status items are shown inTable 41.2.

If the exception did not set a value for an item, an empty string will be returned.

Here is an example:

DECLARE  text_var1 text;  text_var2 text;  text_var3 text;BEGIN  -- some processing which might cause an exception  ...EXCEPTION WHEN OTHERS THEN  GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,                          text_var2 = PG_EXCEPTION_DETAIL,                          text_var3 = PG_EXCEPTION_HINT;END;