OUT parameters in PostgreSQL

Introduction

OUT parameters let a database function return multiple values easily without using complex return types.

When you want a function to give back more than one result.

When you want to avoid creating custom types just to return multiple values.

When you want to write simple reusable functions that return several pieces of data.

When you want to get output values directly without using SELECT inside the function.

Syntax

PostgreSQL

CREATE FUNCTION function_name(IN param1 type, OUT out_param1 type, OUT out_param2 type) RETURNS record AS $$
BEGIN
  -- function body
  out_param1 := value1;
  out_param2 := value2;
END;
$$ LANGUAGE plpgsql;

OUT parameters are declared in the function signature and act like variables inside the function.

The function automatically returns a record containing all OUT parameters.

Examples

This function returns first and last name for a given user ID using OUT parameters.

PostgreSQL

CREATE FUNCTION get_full_name(IN user_id INT, OUT first_name TEXT, OUT last_name TEXT) RETURNS record AS $$
BEGIN
  SELECT fname, lname INTO first_name, last_name FROM users WHERE id = user_id;
END;
$$ LANGUAGE plpgsql;

This function returns the sum and product of two numbers using OUT parameters.

PostgreSQL

CREATE FUNCTION calculate_stats(IN a INT, IN b INT, OUT sum INT, OUT product INT) RETURNS record AS $$
BEGIN
  sum := a + b;
  product := a * b;
END;
$$ LANGUAGE plpgsql;

Sample Program

This example creates a users table, inserts two users, defines a function with OUT parameters to get full name by user ID, and calls it.

PostgreSQL

CREATE TABLE users (id INT, fname TEXT, lname TEXT);
INSERT INTO users VALUES (1, 'Alice', 'Smith'), (2, 'Bob', 'Jones');

CREATE FUNCTION get_full_name(IN user_id INT, OUT first_name TEXT, OUT last_name TEXT) RETURNS record AS $$
BEGIN
  SELECT fname, lname INTO first_name, last_name FROM users WHERE id = user_id;
END;
$$ LANGUAGE plpgsql;

-- Call the function
SELECT * FROM get_full_name(1);

Important Notes

You can call functions with OUT parameters using SELECT * FROM function_name(args);

If you have only OUT parameters and no IN parameters, you can call the function without arguments.

OUT parameters simplify returning multiple values without defining custom types.

Summary

OUT parameters let functions return multiple values easily.

They are declared in the function signature and act like output variables.

Use SELECT * FROM function_name(...) to get the results as columns.