What Is PL/pgSQL Functions in PostgreSQL With Example

PL/pgSQL Functions in PostgreSQL

PL/pgSQL, PostgreSQL’s built-in programming language for server-side functions and procedures, provides a powerful application platform beyond a database. Unlike procedural languages like PL/Perl and PL/Python, which require CREATE EXTENSION or createlang to install, it is included in the standard distribution and installed by default. Designed after Oracle’s PL/SQL, it supports variables, conditional statements (IF-ELSE and CASE), and looping constructs (LOOP, WHILE, FOR) that expand SQL.

PL/pgSQL may perform sophisticated logic directly on the server, eliminating client-server interactions and speeding up execution compared to application-level code. Dynamic SQL, enabled by the EXECUTE command, lets you build SQL statements during runtime, unlike ordinary SQL functions. PL/pgSQL is fully integrated with the PostgreSQL trigger system, allowing developers to design powerful trigger functions that respond to data changes, unlike SQL functions.

Key Features and Advantages of PL/pgSQL Functions

Procedural Capabilities: Process Skills SQL functions generally wrap parameterised SELECT statements, although PL/pgSQL offers sophisticated control structures like IF-THTHEN-ELSE, CASE, LOOP, WHILE, and FOR loops, explicit variable declarations, and assignment statements. This lets the database directly execute sophisticated algorithms and sequential logic.

Error Handling: The RAISE EXCEPTION statement and specific EXCEPTION blocks in PL/pgSQL are used for error handling, preventing unexpected function terminations and enabling recovery or bespoke error messages. The robustness is higher than that of SQL functions.

Dynamic SQL: The EXECUTE command in PL/pgSQL allows for the dynamic construction and execution of SQL statements during runtime. When creating flexible functions where the complete language of a SQL query, like table or view names, might not be available at compilation time this is especially helpful. It is possible to safely create dynamic SQL and guard against SQL injection attacks by using tools like format().

Integration with Trigger System: The PostgreSQL trigger system completely integrates PL/pgSQL trigger functionalities. Custom actions can be automatically done before or after table or view INSERT, UPDATE, or DELETE operations. No SQL functions can use triggers.

Performance and Plan Caching: Execution plans for PL/pgSQL functions are cached, which can save execution time by preventing the need to generate plans repeatedly. But developers need to be aware that if the ideal plan depends heavily on certain parameter values, caching could result in less-than-ideal plans.

Flexibility and Readability: By integrating SQL instructions into modules, PL/pgSQL simplifies complex logic, encourages code reuse, and provides an abstract interface to more advanced programming languages. Similar syntax makes the switch easy for Oracle PL/SQL developers.

CREATE [OR REPLACE] FUNCTION: This creates or replaces a function. Function name, which can be overloaded with arguments. Specifies input, output, or in/out parameters with parameter_name and parameter_type.

Return_type: Return_type is a parameter that indicates the data type of the value or values that the function will return. For functions that return several columns or rows, this can be TABLE(…), VOID (if no value is provided), SETOF (for set-returning functions), base types, or composite types.

SPEAK : The function’s writing language is indicated by plpgsql. The volatility classifications VOLATILE | STABLE | IMMUTABLE aid the query optimiser. For the same inputs, IMMUTABLE functions always yield the same result and have no adverse effects. Although they don’t change the database, stable functions can produce various results depending on the contents of distinct tables. While the calling query is running, they see a fixed view of the database. VOLATILE (the default) functions may provide inconsistent results and have adverse impacts.

DECLARE: An optional section for defining sophisticated data types (such as %TYPE and RECORD), local variables, and parameter aliases. Defines the executable body of the function with BEGIN… END. These are for specifying variable scope and grouping statements, not for transaction control. To allocate a SELECT query’s outcome to one or more variables, use the SELECT INTO operator.

RETURN: Returning a value, RETURN exits the function. For functions that return a set, RETURN NEXT is used for single rows, and RETURN QUERY is used for the result of a SELECT operation.

Code Example:

An example of a PL/pgSQL function to determine an integer’s factorial is as follows:

CREATE OR REPLACE FUNCTION fact(input_number INT) RETURNS INT AS
DECLARE
    -- Declare local variables for the loop counter and the result
    count_var INT = 1;
    result_var INT = 1;
BEGIN
    -- Check for invalid input (e.g., negative numbers)
    IF input_number IS NULL OR input_number < 0 THEN
        RAISE NOTICE 'Invalid Number: Input must be a non-negative integer.';
        RETURN NULL;
    ELSIF input_number = 0 OR input_number = 1 THEN
        RETURN 1;
    ELSE
        -- Loop from 1 up to the input_number
        FOR count_var IN 1..input_number LOOP
            result_var = result_var * count_var; -- Assignment statement
        END LOOP;
        RETURN result_var; -- Return the final calculated factorial
    END IF;
END;
LANGUAGE plpgsql;

Output:

CREATE FUNCTION

Explanation of the example

CREATE OR REPLACE FUNCTION fact(input_number INT) RETURNS INT: This line defines a function called fact that returns an integer after accepting an integer as input_number. If a function with this name and signature already exists, OR REPLACE guarantees that it will be changed.

DECLARE: Two local variables, count_var and result_var, are declared in this block. Both variables are initialised to 1 and serve as a loop counter and store the calculated factorial, respectively.

IF … ELSIF … ELSE … END IF;: Various input scenarios are handled by this conditional structure: It raises a notice and returns NULL if input_number is negative or NULL. Since 0! = 1 and 1! = 1, it returns 1 immediately if input_number is either 0 or 1. It then uses a loop to compute the factorial for positive integers larger than 1.

FOR count_var IN 1..input_number LOOP … END LOOP;: The enclosed code block is repeatedly run by this iteration control statement. The values of count_var will range from 1 to input_number.

result_var = result_var * count_var;: This assignment statement multiplies result_var by the current value of count_var to update it in each iteration.

RETURN result_var;: This statement leaves the function and returns the result_var’s final value when the loop is finished.

LANGUAGE plpgsql;: Indicates that PL/pgSQL is used to write the function body.

Conclusion

PostgreSQL’s PL/pgSQL functions offer a reliable means of carrying out procedural logic inside the database server, removing needless client-server interactions and enhancing efficiency. They are significantly more flexible than standard SQL functions since they support variable declarations, conditional statements, loops, error handling, and dynamic SQL. When PL/pgSQL functions are fully connected with PostgreSQL trigger system, they can automate intricate activities in reaction to changes in the data.

Developers have fine-grained control over optimisation and behaviour with features like execution plan caching, volatility classifications (IMMUTABLE, STABLE, VOLATILE), and stringent null handling. PL/pgSQL functions allow developers to write small, manageable, and high-performance code for business logic, automation, and data processing inside the database itself by fusing SQL with procedural tools.