How to Use OUT Parameter in Function in PostgreSQL

In PostgreSQL, you can use OUT parameters in a function to return values directly without using RETURN. Define the function with OUT parameters in the signature, assign values to them inside the function, and call the function like a query to get the results.

Syntax

The OUT parameter in PostgreSQL functions allows you to specify output variables directly in the function signature. These parameters act like variables that the function sets and returns automatically.

Syntax parts:

• CREATE FUNCTION: starts the function definition.
• Function name and input parameters (if any).
• OUT param_name param_type: declares an output parameter.
• AS $$ ... $$: function body in PL/pgSQL.
• LANGUAGE plpgsql: specifies the language.

sql

CREATE FUNCTION function_name( input_parameters )
RETURNS TABLE (out_param1 type1, out_param2 type2, ...) AS $$
BEGIN
  -- assign values to out parameters
  out_param1 := value1;
  out_param2 := value2;
END;
$$ LANGUAGE plpgsql;

Example

This example shows a function that takes an integer and returns its square and cube using OUT parameters. You call it like a query and get the results as columns.

sql

CREATE OR REPLACE FUNCTION calculate_powers(
  input_num integer,
  OUT square integer,
  OUT cube integer
) AS $$
BEGIN
  square := input_num * input_num;
  cube := input_num * input_num * input_num;
END;
$$ LANGUAGE plpgsql;

-- Call the function
SELECT * FROM calculate_powers(3);

Output

 square | cube 
--------+------
      9 |   27
(1 row)

Common Pitfalls

Common mistakes when using OUT parameters include:

• Not assigning a value to the OUT parameter inside the function, which results in NULL outputs.
• Using RETURN with a value in a function that has OUT parameters, which is not needed and causes errors.
• Confusing OUT parameters with IN parameters; OUT parameters are for output only.

Correct usage assigns values to OUT parameters and ends the function without a RETURN statement.

sql

/* Wrong: Using RETURN with OUT parameters */
CREATE FUNCTION wrong_func(OUT result integer) AS $$
BEGIN
  result := 10;
  RETURN; -- This causes error if RETURN has a value
END;
$$ LANGUAGE plpgsql;

/* Right: Assign to OUT parameter and no RETURN */
CREATE FUNCTION right_func(OUT result integer) AS $$
BEGIN
  result := 10;
END;
$$ LANGUAGE plpgsql;

Quick Reference

Concept	Description	Example
OUT Parameter	Declares output variable in function signature	OUT result integer
Assigning Value	Set value inside function body	result := 42;
Calling Function	Use SELECT to get output columns	SELECT * FROM function_name(args);
No RETURN Needed	Function returns OUT params automatically	No RETURN statement required

Key Takeaways

Use OUT parameters in PostgreSQL functions to return values directly from the signature.

Assign values to OUT parameters inside the function body without using RETURN.

Call functions with OUT parameters using SELECT to get the output as columns.

Avoid returning values explicitly when using OUT parameters to prevent errors.

OUT parameters simplify returning multiple values from a function.