Returning a Value¶
To return a value, use the RETURN command. You can return a value from:
A block in a stored procedure.
An anonymous block.
You can return a value of one of the following types:
A table. Use
TABLE(...)
in theRETURN
statement.If your block is in a stored procedure, you must also specify the
RETURNS TABLE...
clause in the CREATE PROCEDURE statement.Note
Currently, in the
RETURNS TABLE(...)
clause, you cannot specify GEOGRAPHY as a column type. This applies whether you’re creating a stored or anonymous procedure.CREATE OR REPLACE PROCEDURE test_return_geography_table_1() RETURNS TABLE(g GEOGRAPHY) ...
WITH test_return_geography_table_1() AS PROCEDURE RETURNS TABLE(g GEOGRAPHY) ... CALL test_return_geography_table_1();
If you attempt to specify GEOGRAPHY as a column type, calling the stored procedure results in the error:
Stored procedure execution error: data type of returned table does not match expected returned table type
To work around this, you can omit the column arguments and types in
RETURNS TABLE()
.CREATE OR REPLACE PROCEDURE test_return_geography_table_1() RETURNS TABLE() ...
WITH test_return_geography_table_1() AS PROCEDURE RETURNS TABLE() ... CALL test_return_geography_table_1();
If you want to return the data that a RESULTSET points to, pass the
RESULTSET
toTABLE(...)
, as shown in the example below:create procedure ... returns table(...) ... return table(my_result_set); ...