PostgreSQL Polymorphic Functions

Introduction

In PostgreSQL, polymorphic functions are a powerful feature that allows you to create a single function capable of operating on multiple data types. Instead of writing separate functions for each data type, polymorphic functions let you write code once that works with various input types.

This concept is particularly valuable when you need to perform similar operations across different data types, helping you maintain cleaner and more maintainable code by reducing duplication.

Understanding Polymorphism in PostgreSQL

Polymorphism comes from the Greek words "poly" (many) and "morph" (form), meaning "many forms." In PostgreSQL, this refers to functions that can take arguments of different types.

PostgreSQL supports two types of polymorphism:

1. Pseudotypes - Special placeholder types that represent "any" type
2. Polymorphic Arguments - Arguments that can accept multiple data types

Polymorphic Pseudotypes

PostgreSQL provides several special pseudotypes for creating polymorphic functions:

Pseudotype	Description
anyelement	Represents any single data type
anyarray	Represents any array type
anynonarray	Represents any non-array type
anyenum	Represents any enum type
anyrange	Represents any range type

Creating Basic Polymorphic Functions

Let's start with a simple example: a function that accepts two values of any type and returns the first one.

sql

CREATE OR REPLACE FUNCTION return_first(a anyelement, b anyelement) 
RETURNS anyelement AS $$
BEGIN
    RETURN a;
END;
$$ LANGUAGE plpgsql;

This function can be called with any data types, as long as both arguments are of the same type:

sql

SELECT return_first(1, 2);         -- Returns: 1 (integer)
SELECT return_first('a', 'b');     -- Returns: 'a' (text)
SELECT return_first(1.5, 2.5);     -- Returns: 1.5 (numeric)
SELECT return_first(TRUE, FALSE);  -- Returns: TRUE (boolean)

How Type Resolution Works

When you call a polymorphic function, PostgreSQL determines the actual types for the polymorphic parameters at runtime:

1. PostgreSQL examines the actual types of all arguments provided
2. It tries to find a common type that all arguments can be converted to
3. If successful, it resolves the polymorphic parameters to that type
4. If not, it raises an error

Working with Different Polymorphic Types

Let's create a more useful example: a function that can swap two values of any type.

sql

CREATE OR REPLACE FUNCTION swap(
    INOUT a anyelement, 
    INOUT b anyelement
) AS $$
DECLARE
    temp alias for a;
BEGIN
    a := b;
    b := temp;
END;
$$ LANGUAGE plpgsql;

You can use this function with various data types:

sql

SELECT * FROM swap(10, 20);          -- Returns: a=20, b=10
SELECT * FROM swap('hello', 'world'); -- Returns: a='world', b='hello'

Arrays and Polymorphism

The anyarray pseudotype lets you create functions that work with any array type:

sql

CREATE OR REPLACE FUNCTION first_element(arr anyarray) 
RETURNS anyelement AS $$
BEGIN
    RETURN arr[1];
END;
$$ LANGUAGE plpgsql;

This function returns the first element from any array:

sql

SELECT first_element(ARRAY[1,2,3]);          -- Returns: 1
SELECT first_element(ARRAY['a','b','c']);    -- Returns: 'a'
SELECT first_element(ARRAY[[1,2],[3,4]]);    -- Returns: [1,2]

Practical Examples

Example 1: Generic Max Function

Let's create a function that returns the maximum of two values of any type:

sql

CREATE OR REPLACE FUNCTION generic_max(a anyelement, b anyelement) 
RETURNS anyelement AS $$
BEGIN
    IF a > b THEN
        RETURN a;
    ELSE
        RETURN b;
    END IF;
END;
$$ LANGUAGE plpgsql;

Usage examples:

sql

SELECT generic_max(5, 10);          -- Returns: 10
SELECT generic_max('apple', 'zebra'); -- Returns: 'zebra'
SELECT generic_max(5.5, 2.2);       -- Returns: 5.5

Example 2: Array Utilities

Here's a function that creates a single-element array of any type:

sql

CREATE OR REPLACE FUNCTION make_array(element anyelement) 
RETURNS anyarray AS $$
BEGIN
    RETURN ARRAY[element];
END;
$$ LANGUAGE plpgsql;

Usage examples:

sql

SELECT make_array(42);         -- Returns: {42}
SELECT make_array('hello');    -- Returns: {hello}
SELECT make_array(NOW());      -- Returns: {2023-09-12 10:30:00}

Example 3: Generic Collection Functions

A function to append an element to an array of any type:

sql

CREATE OR REPLACE FUNCTION append_element(
    arr anyarray,
    elem anyelement
) 
RETURNS anyarray AS $$
BEGIN
    RETURN array_append(arr, elem);
END;
$$ LANGUAGE plpgsql;

Usage examples:

sql

SELECT append_element(ARRAY[1,2,3], 4);           -- Returns: {1,2,3,4}
SELECT append_element(ARRAY['a','b'], 'c');       -- Returns: {a,b,c}
SELECT append_element(ARRAY[TRUE], FALSE);        -- Returns: {t,f}

Real-World Applications

Example 1: Database Logging System

A polymorphic function for logging changes to any table column:

sql

CREATE OR REPLACE FUNCTION log_change(
    table_name text,
    column_name text,
    old_value anyelement,
    new_value anyelement
) RETURNS void AS $$
BEGIN
    INSERT INTO change_log (
        table_name, 
        column_name, 
        old_value_text, 
        new_value_text, 
        change_date
    ) VALUES (
        table_name,
        column_name,
        old_value::text,
        new_value::text,
        NOW()
    );
END;
$$ LANGUAGE plpgsql;

This function converts any type of old/new values to text for storage in a log table.

Example 2: Generic Data Validation

A function that validates values against min/max thresholds of any comparable type:

sql

CREATE OR REPLACE FUNCTION validate_range(
    value anyelement,
    min_value anyelement,
    max_value anyelement
) RETURNS boolean AS $$
BEGIN
    RETURN value >= min_value AND value <= max_value;
END;
$$ LANGUAGE plpgsql;

Usage examples:

sql

SELECT validate_range(5, 1, 10);              -- Returns: TRUE
SELECT validate_range('c', 'a', 'f');         -- Returns: TRUE
SELECT validate_range(DATE '2023-05-15', 
                      DATE '2023-01-01', 
                      DATE '2023-12-31');     -- Returns: TRUE

Limitations and Considerations

When working with polymorphic functions, keep these limitations in mind:

1. Type Consistency: All arguments of a given polymorphic type must resolve to the same actual type.

2. Type Safety: While polymorphic functions are flexible, they can sometimes hide type-related bugs.

3. Performance: In some cases, type-specific functions might perform better than polymorphic ones.

4. Return Type Resolution: The return type must be determinable from the input argument types.

5. Error Handling: Type mismatches generate runtime errors, so thorough testing is important.

Common Patterns and Best Practices

1. Document Type Requirements: Always document which types your polymorphic function supports.

2. Type Checking: Include explicit checks if your function only works with certain types.

   sql

   IF pg_typeof(value) NOT IN ('integer'::regtype, 'numeric'::regtype) THEN
       RAISE EXCEPTION 'Unsupported data type: %', pg_typeof(value);
   END IF;

3. Function Overloading: Consider using function overloading alongside polymorphism for complex cases.

4. Testing: Test polymorphic functions with all expected data types.

Summary

PostgreSQL polymorphic functions provide a powerful way to write generic, reusable code that works with multiple data types. By using pseudotypes like anyelement and anyarray, you can create versatile functions that reduce code duplication and increase maintainability.

Key benefits include:

• Writing code once that works with many data types
• Creating generic utility functions for collections and values
• Implementing flexible business logic that isn't tied to specific types

As you develop your PostgreSQL skills, polymorphic functions will become an important tool for creating more elegant and maintainable database code.

Additional Resources

• PostgreSQL Official Documentation on Polymorphic Types
• PostgreSQL Type System
• PL/pgSQL Language

Exercises

1. Create a polymorphic function that returns the second element of any array.

2. Write a function that checks if two values of any type are equal and returns a boolean.

3. Implement a function that takes three arguments of the same type and returns the middle (median) value.

4. Create a generic function that reverses any array.

5. Develop a function that safely converts between compatible types using polymorphic arguments.