PL/pgSQL Function Overloading

Master function overloading — multiple functions sharing a name distinguished by argument signature. Covers convenience overloads with extra parameters, type-based overloading for ID-or-name lookups, and the ambiguity trap when overloading combines with parameter defaults.

Overloading is having multiple functions that share the same name but differ in their argument lists. PostgreSQL distinguishes them by their full signature: function_name(arg_types). Calls are dispatched based on the types of the arguments supplied.

Three common reasons to overload:

• Different argument counts — a base version with required args, plus convenience versions that take fewer (or more) parameters
• Different argument types — accept either an ID or a name to look something up
• Different argument shapes — same logical operation on a scalar vs. an array

Just create multiple functions with the same name but different parameter lists:

PostgreSQL stores them as separate functions internally — they each have a unique signature even though they share a name. When you call name(...), PostgreSQL looks at the argument types and picks the matching version.

DROP TABLE IF EXISTS rental;
DROP TABLE IF EXISTS customer;

CREATE TABLE customer (
    customer_id INT PRIMARY KEY,
    name        VARCHAR(100),
    email       VARCHAR(100)
);

CREATE TABLE rental (
    rental_id   INT PRIMARY KEY,
    customer_id INT,
    rental_date DATE,
    return_date DATE
);

INSERT INTO customer (customer_id, name, email) VALUES
    (1, 'John Doe',      'john@example.com'),
    (2, 'Jane Smith',    'jane@example.com'),
    (3, 'Alice Johnson', 'alice@example.com'),
    (4, 'Bob Brown',     'bob@example.com'),
    (5, 'Eva White',     'eva@example.com');

INSERT INTO rental (rental_id, customer_id, rental_date, return_date) VALUES
    (100, 1, '2024-05-01', '2024-05-05'),
    (200, 2, '2024-05-02', '2024-05-07'),
    (300, 1, '2024-06-01', '2024-06-04'),
    (400, 3, '2024-05-03', '2024-05-06'),
    (500, 2, '2024-06-15', '2024-06-20'),
    (600, 4, '2024-05-04', '2024-05-08'),
    (700, 5, '2024-05-05', '2024-05-09');

Total rental days for a given customer, all-time:

DROP FUNCTION IF EXISTS get_rental_duration(INT);
DROP FUNCTION IF EXISTS get_rental_duration(INT, DATE);

CREATE OR REPLACE FUNCTION get_rental_duration(p_customer_id INT)
RETURNS INT
LANGUAGE plpgsql
AS $$
DECLARE
    rental_days INT;
BEGIN
    SELECT COALESCE(SUM(return_date - rental_date), 0)::INT
    INTO   rental_days
    FROM   rental
    WHERE  customer_id = p_customer_id;

    RETURN rental_days;
END;
$$;

SELECT get_rental_duration(1);   -- John Doe, all rentals

John has two rentals: 4 days (May 1-5) + 3 days (June 1-4) = 7 total.

Now add a version that filters rentals to those starting on or after a given date. Same name, different argument list:

CREATE OR REPLACE FUNCTION get_rental_duration(
    p_customer_id INT,
    p_from_date   DATE
)
RETURNS INT
LANGUAGE plpgsql
AS $$
DECLARE
    rental_days INT;
BEGIN
    SELECT COALESCE(SUM(return_date - rental_date), 0)::INT
    INTO   rental_days
    FROM   rental
    WHERE  customer_id = p_customer_id
      AND  rental_date >= p_from_date;

    RETURN rental_days;
END;
$$;

-- Same name, different signature — PostgreSQL picks based on arg types
SELECT get_rental_duration(1, '2024-06-01');   -- John, only June onward

Now we get only 3 — John's June rental (3 days), excluding the May one. Two functions, same name, dispatched based on whether you pass one or two arguments.

A common pattern: same function logic, but accept different argument types. Let callers pass either a customer ID (INT) or a customer name (VARCHAR):

DROP FUNCTION IF EXISTS get_email(INT);
DROP FUNCTION IF EXISTS get_email(VARCHAR);

-- Look up by customer ID
CREATE OR REPLACE FUNCTION get_email(p_id INT)
RETURNS VARCHAR
LANGUAGE plpgsql
AS $$
DECLARE
    result VARCHAR;
BEGIN
    SELECT email INTO result FROM customer WHERE customer_id = p_id;
    RETURN result;
END;
$$;

-- Look up by customer name
CREATE OR REPLACE FUNCTION get_email(p_name VARCHAR)
RETURNS VARCHAR
LANGUAGE plpgsql
AS $$
DECLARE
    result VARCHAR;
BEGIN
    SELECT email INTO result FROM customer WHERE name = p_name;
    RETURN result;
END;
$$;

SELECT get_email(2)              AS by_id,
       get_email('Eva White')    AS by_name;

Callers don't have to remember which lookup function name to use — they just call get_email with whatever they have, and PostgreSQL picks the right version.

Mixing overloading with parameter defaults can produce ambiguous calls. If you have:

• get_rental_duration(INT)
• get_rental_duration(INT, DATE)
• get_rental_duration(INT, DATE DEFAULT '2020-01-01')

...then a call with one argument, like get_rental_duration(1), could match either the first function or the third (using the default for the second arg). PostgreSQL refuses to guess:

1. Reach for overloading when the same logical operation accepts genuinely different argument types. Looking up "by ID or by name" is a good case.
2. Avoid mixing overloading with defaults that overlap arities. Function resolution gets ambiguous; PostgreSQL throws errors and your callers can't predict which version runs.
3. Use distinct names when the operations differ semantically. Just because two things "kind of relate" doesn't mean they should share a name.
4. Use DROP FUNCTION IF EXISTS function_name(arg_types); in deployment scripts — drops only the specific signature, leaving other overloads intact.
5. Be careful with type families. (INT) and (BIGINT) can be ambiguous when the literal is a small number — PostgreSQL has to pick a coercion. Avoid overloading on closely-related numeric types.
6. Document the overload set with comments at each function's site, so a reader sees the variations together.

• Overloading lets multiple functions share a name, distinguished by their argument signatures.
• PostgreSQL dispatches calls by matching argument types to function signatures.
• Use overloading when the same logical operation accepts different argument types — looking up by ID vs by name, scalar vs array.
• Avoid combining overloading with defaults that create ambiguous arities — function resolution will fail with "not unique" errors.
• For dropping a specific overload, include the argument list: DROP FUNCTION name(arg_types).
• When in doubt, distinct function names are clearer and easier to deprecate. Reach for overloading deliberately, not by default.