Stored procedure calls
PostgreSQL supports stored procedures and stored functions.
CREATE PROCEDURE
statement. Stored procudes can take IN
, OUT
and
INOUT
parameters that will be returned as a result set row.IN
and INOUT
parameter markers are supported with
stored procedures, requiring to use the INOUT
marker for output-only parameters.
Starting with PostgreSQL version 14, you can use the OUT
parameter marker, for
output-only parameters.To create a stored function in a PortgreSQL database, use the CREATE FUNCTION
statement. Stored functions can take IN
parameters and can also return more that
one value when specify the returning values as function parameters with the OUT
keyword. However, to be a scalar valued function that can be used in SQL expressions, a stored
function must return a single value with the RETURNS
clause. To return a resut set
with multiple rows, define a stored function with the RETURNS SETOF
clause.
Pay attention to the procedure/function signature; PostgreSQL allows overloading. For example,
func(int)
and func(char)
are two different functions. To drop a
procedure or function, you must specify the parameter type to identify signature properly.