How to Fix PostgreSQL Error: Function Does Not Exist

The "Function does not exist" error occurs when calling a function that PostgreSQL cannot find, either because it doesn't exist, has different argument types than provided, or is in a schema not in the search path. PostgreSQL functions are identified by name AND argument types.

Impact

This error prevents query execution and can break applications, stored procedures, and migrations. It often occurs after upgrades, during development, or when working with extensions.

Common Causes

Function not created or extension not installed
Argument type mismatch (function signature)
Wrong number of arguments
Function in different schema than search_path
Case-sensitive function names (quoted)
Missing type casts
PostgreSQL version differences

Troubleshooting and Resolution Steps

List available functions:

-- List all functions
\df

-- Search for specific function
\df function_name

-- Show function details with argument types
\df+ function_name

-- Query system catalog
SELECT
    n.nspname AS schema,
    p.proname AS function_name,
    pg_get_function_identity_arguments(p.oid) AS arguments
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE p.proname LIKE '%function_name%';

Fix argument type mismatches:

-- ERROR: function does not exist
SELECT my_function(123);  -- Passing integer

-- If function expects TEXT
SELECT my_function('123');  -- Pass string
-- Or cast
SELECT my_function(123::TEXT);
SELECT my_function(CAST(123 AS TEXT));

-- ERROR: function myfunction(character varying) does not exist
-- HINT: No function matches the given name and argument types.
--       You might need to add explicit type casts.

-- Fix with explicit cast
SELECT myfunction(column_name::INTEGER);

Create missing function:

-- Simple function example
CREATE OR REPLACE FUNCTION calculate_total(price NUMERIC, quantity INTEGER)
RETURNS NUMERIC AS $$
BEGIN
    RETURN price * quantity;
END;
$$ LANGUAGE plpgsql;

-- Usage
SELECT calculate_total(10.50, 5);

Install missing extension:

-- Check available extensions
SELECT * FROM pg_available_extensions
WHERE name LIKE '%extension_name%';

-- Install extension
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Now functions are available
SELECT gen_random_uuid();

Fix schema path issues:

-- Check current search path
SHOW search_path;

-- Add schema to search path
SET search_path TO myschema, public;

-- Or use schema-qualified name
SELECT myschema.my_function(123);

-- Set permanently for user
ALTER USER myuser SET search_path TO myschema, public;

Handle overloaded functions:

-- PostgreSQL allows function overloading
-- Same name, different argument types

CREATE FUNCTION process(x INTEGER) RETURNS INTEGER AS $$
    SELECT x * 2;
$$ LANGUAGE SQL;

CREATE FUNCTION process(x TEXT) RETURNS TEXT AS $$
    SELECT UPPER(x);
$$ LANGUAGE SQL;

-- Must call with correct type
SELECT process(5);        -- Calls INTEGER version
SELECT process('hello');  -- Calls TEXT version
SELECT process(5::TEXT);  -- Calls TEXT version

Common casting issues:

-- String functions
SELECT LENGTH(123);  -- ERROR
SELECT LENGTH(123::TEXT);  -- OK

-- Date functions
SELECT DATE_PART('year', '2024-01-01');  -- ERROR
SELECT DATE_PART('year', '2024-01-01'::DATE);  -- OK
SELECT DATE_PART('year', DATE '2024-01-01');  -- OK

-- JSON functions
SELECT jsonb_array_length('[1,2,3]');  -- ERROR
SELECT jsonb_array_length('[1,2,3]'::JSONB);  -- OK

Create function with proper types:

-- Define function with specific types
CREATE OR REPLACE FUNCTION get_user_age(user_id INTEGER)
RETURNS INTEGER AS $$
DECLARE
    birth_date DATE;
    current_age INTEGER;
BEGIN
    SELECT date_of_birth INTO birth_date
    FROM users
    WHERE id = user_id;

    current_age := DATE_PART('year', AGE(birth_date));

    RETURN current_age;
END;
$$ LANGUAGE plpgsql;

-- Call with correct type
SELECT get_user_age(123);        -- OK
SELECT get_user_age('123'::INT); -- OK with cast
SELECT get_user_age('123');      -- ERROR

Check function existence before calling:

-- Check if function exists
SELECT EXISTS (
    SELECT 1
    FROM pg_proc p
    JOIN pg_namespace n ON p.pronamespace = n.oid
    WHERE n.nspname = 'public'
    AND p.proname = 'my_function'
);

-- Drop if exists
DROP FUNCTION IF EXISTS my_function(INTEGER);

Application-level handling:

# Python with psycopg2
from psycopg2 import ProgrammingError

try:
    cursor.execute("SELECT my_custom_function(%s)", (value,))
    result = cursor.fetchone()[0]
except ProgrammingError as e:
    if 'function' in str(e) and 'does not exist' in str(e):
        print("Function not available, using fallback")
        # Use alternative logic
        result = alternative_calculation(value)
    else:
        raise

Additional Information

Function signatures include name AND argument types
PostgreSQL is case-insensitive for unquoted identifiers
Extensions must be installed to use their functions
Built-in functions vary between PostgreSQL versions
Schema search_path affects function resolution
Use schema-qualified names for clarity
Document custom functions and their signatures
Consider function overloading carefully

Frequently Asked Questions

Q: Why does SELECT LENGTH(123) fail?
A: LENGTH expects text, not integer. Use LENGTH(123::TEXT) or LENGTH(CAST(123 AS TEXT)).

Q: How do I see what argument types a function expects?
A: Use \df+ function_name in psql or query pg_proc system catalog.

Q: Can I have two functions with the same name?
A: Yes, if they have different argument types (function overloading). PostgreSQL selects based on argument types.

Q: Why can't I call a function from an extension?
A: Install the extension first: CREATE EXTENSION extension_name;

Q: What if function exists but in different schema?
A: Either add schema to search_path or use schema-qualified name: schema_name.function_name()

Q: How do I fix "No function matches the given name and argument types"?
A: Add explicit type casts to your arguments to match the function signature.

Q: Can function names be case-sensitive?
A: Only if quoted during creation. CREATE FUNCTION "MyFunc"() is different from myFunc().

Q: What's the difference between SQL and plpgsql functions?
A: SQL functions are simpler (single query). plpgsql allows variables, control flow, and multiple statements.