Stored procedure calls

PostgreSQL supports stored procedures and stored functions.

Important: Stored procedures are supported since PostgreSQL version 11: Only stored functions are supported in releases prior to PostgreSQL 11.

To create a stored procedure in a PortgreSQL database, use the CREATE PROCEDURE statement. Stored procudes can take IN parameter and INOUT parameters that will be returned as a result set row.

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.

Note: 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.