PostgreSQL Tutorial

Data Types

Querying & Filtering Data

Managing Tables

Modifying Data

Conditionals

Control Flow

Transactions & Constraints

Working with JOINS & Schemas

Roles & Permissions

Working with Sets

Subquery & CTEs

User-defined Functions

Important In-Built Functions

PostgreSQL PL/pgSQL

Variables & Constants

Stored Procedures

Working with Triggers

Working with Views & Indexes

Errors & Exception Handling

PostgreSQL - Function parameter modes

In PostgreSQL, when creating functions, you can specify parameter modes for the function parameters. The parameter modes define how the arguments can be used inside the function body. There are three primary parameter modes:

IN:
- This is the default mode.
- The parameter can be used within the function to get the value passed in.
- Its value cannot be changed inside the function.
OUT:
- This mode specifies that the parameter is a return value.
- A function can have multiple OUT parameters, effectively allowing it to return multiple columns.
- An OUT parameter can be assigned a value inside the function, which will be returned to the caller.
INOUT:
- This mode means the parameter can be used both as an input and an output.
- The caller provides an initial value, and the function can modify it. The final value will be returned to the caller.

Examples:

Using IN (implicit) and OUT parameters:

CREATE OR REPLACE FUNCTION divide_numbers(numerator double precision, denominator double precision, result OUT double precision, remainder OUT double precision)
AS $$
BEGIN
    result := numerator / denominator;
    remainder := mod(numerator, denominator);
END;
$$ LANGUAGE plpgsql;

SELECT * FROM divide_numbers(10, 3);

This function takes two numbers, divides them, and returns both the result and the remainder.

Using INOUT parameter:

CREATE OR REPLACE FUNCTION increment_value(INOUT number integer)
AS $$
BEGIN
    number := number + 1;
END;
$$ LANGUAGE plpgsql;

SELECT increment_value(5);

This function takes a number, increments it, and returns the incremented value.

It's essential to understand how to use these parameter modes effectively to create flexible and modular functions in PostgreSQL. While IN and OUT parameters are more commonly used, INOUT can be beneficial when you want to transform a given input and send it back as part of the output.

IN parameter mode in PostgreSQL functions:

Description: Accept input values for computation within the function.

Code:

CREATE OR REPLACE FUNCTION calculate_square(IN x INTEGER)
RETURNS INTEGER AS $$
BEGIN
    RETURN x * x;
END;
$$ LANGUAGE plpgsql;

OUT parameter mode in PostgreSQL functions:

Description: Return values from the function to the caller.

Code:

CREATE OR REPLACE FUNCTION generate_greeting(OUT greeting TEXT)
AS $$
BEGIN
    greeting := 'Hello, World!';
END;
$$ LANGUAGE plpgsql;

INOUT parameter mode in PostgreSQL functions:

Description: Accept input values and return modified values.

Code:

CREATE OR REPLACE FUNCTION increment_value(INOUT x INTEGER, IN increment_by INTEGER)
AS $$
BEGIN
    x := x + increment_by;
END;
$$ LANGUAGE plpgsql;

VARIADIC parameter mode in PostgreSQL functions:

Description: Accept a variable number of arguments as an array.

Code:

CREATE OR REPLACE FUNCTION concatenate_strings(VARIADIC strings TEXT[])
RETURNS TEXT AS $$
BEGIN
    RETURN array_to_string(strings, ' ');
END;
$$ LANGUAGE plpgsql;

How to use different parameter modes in PostgreSQL functions:

Description: Combine various parameter modes in a function.

Code:

CREATE OR REPLACE FUNCTION process_data(IN input_data INTEGER, OUT result_data INTEGER, INOUT status TEXT)
AS $$
BEGIN
    -- Processing logic
    result_data := input_data * 2;
    status := 'Processed';
END;
$$ LANGUAGE plpgsql;

Mixing parameter modes in PostgreSQL function parameters:

Description: Use a combination of different parameter modes in a function.

Code:

CREATE OR REPLACE FUNCTION mix_parameter_modes(IN x INTEGER, OUT doubled INTEGER, INOUT status TEXT)
AS $$
BEGIN
    doubled := x * 2;
    status := 'Processed';
END;
$$ LANGUAGE plpgsql;

Passing values by reference in PostgreSQL functions:

Description: Use the INOUT parameter mode to pass values by reference.

Code:

CREATE OR REPLACE FUNCTION increment_by_reference(INOUT x INTEGER, IN increment_by INTEGER)
AS $$
BEGIN
    x := x + increment_by;
END;
$$ LANGUAGE plpgsql;

Default values with parameter modes in PostgreSQL functions:

Description: Assign default values to parameters.

Code:

CREATE OR REPLACE FUNCTION greet_person(INOUT name TEXT DEFAULT 'Guest', OUT greeting TEXT)
AS $$
BEGIN
    greeting := 'Hello, ' || name || '!';
END;
$$ LANGUAGE plpgsql;

Returning multiple values with OUT and INOUT modes in PostgreSQL:

Description: Use multiple OUT and INOUT parameters to return multiple values.

Code:

CREATE OR REPLACE FUNCTION calculate_stats(IN x INTEGER, OUT sum INTEGER, OUT average FLOAT)
AS $$
BEGIN
    -- Calculation logic
    sum := x + 10;
    average := (x + 10) / 2.0;
END;
$$ LANGUAGE plpgsql;

Using VARIADIC mode for variable-length argument lists in PostgreSQL:

Description: Accept a variable number of arguments using the VARIADIC mode.

Code:

CREATE OR REPLACE FUNCTION sum_numbers(VARIADIC numbers INTEGER[])
RETURNS INTEGER AS $$
BEGIN
    RETURN COALESCE(SUM(numbers), 0);
END;
$$ LANGUAGE plpgsql;

Input and output parameters in PostgreSQL functions:

Description: Combine IN, OUT, and INOUT parameters for comprehensive functionality.

Code:

CREATE OR REPLACE FUNCTION process_data(IN input_data INTEGER, OUT result_data INTEGER, INOUT status TEXT)
AS $$
BEGIN
    -- Processing logic
    result_data := input_data * 2;
    status := 'Processed';
END;
$$ LANGUAGE plpgsql;