Returns available flights or raises exception if there are no Since execution is not finished, we can check whether rows were returned Here is an example of a function using RETURN QUERY:ĬREATE FUNCTION get_available_flightid(date) RETURNS SETOF integer AS RETURN NEXT r - return current row of SELECT Here is an example of a function using RETURN NEXT:ĬREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT) ĬREATE OR REPLACE FUNCTION get_all_foo() RETURNS SETOF foo AS Note that you must declare the function as returning SETOF record when there are multiple output parameters, or SETOF sometype when there is just one output parameter of type sometype, in order to create a set-returning function with output parameters. On each execution, the current values of the output parameter variable(s) will be saved for eventual return as a row of the result. If you declared the function with output parameters, write just RETURN NEXT with no expression. Parameter expressions can be inserted into the computed query string via USING, in just the same way as in the EXECUTE command. RETURN QUERY has a variant RETURN QUERY EXECUTE, which specifies the query to be executed dynamically. A final RETURN, which should have no argument, causes control to exit the function (or you can just let control reach the end of the function). As successive RETURN NEXT or RETURN QUERY commands are executed, the result set is built up. Execution then continues with the next statement in the PL/pgSQL function. RETURN NEXT and RETURN QUERY do not actually return from the function - they simply append zero or more rows to the function's result set. RETURN NEXT and RETURN QUERY can be freely intermixed in a single set-returning function, in which case their results will be concatenated. RETURN QUERY appends the results of executing a query to the function's result set. 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.

In that case, the individual items to return are specified by a sequence of RETURN NEXT or RETURN QUERY commands, and then a final RETURN command with no argument is used to indicate that the function has finished executing.

When a PL/pgSQL function is declared to return SETOF sometype, the procedure to follow is slightly different. RETURN (1, 2, 'three'::text) - must cast columns to correct types In those cases a RETURN statement is automatically executed if the top-level block finishes.

This restriction does not apply to functions with output parameters and functions returning void, however. If control reaches the end of the top-level block of the function without hitting a RETURN statement, a run-time error will occur. The return value of a function cannot be left undefined. If you declared the function to return void, a RETURN statement can be used to exit the function early but do not write an expression following RETURN. The current values of the output parameter variables will be returned. If you declared the function with output parameters, write just RETURN with no expression. This may require use of explicit casting. But to return a composite (row) value, you must write an expression delivering exactly the requested column 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. This form is used for PL/pgSQL functions that do not return a set. RETURN with an expression terminates the function and returns the value of expression to the caller.

Postgresql if then else