function does not exist

This error occurs when you call a function that doesn't exist or when the function signature doesn't match.

The function does not exist error means PostgreSQL can't find a function matching your call.

Understanding the Error

ERROR: function my_function(integer) does not exist
HINT: No function matches the given name and argument types.

Either the function doesn't exist or you're calling it with wrong argument types.

Common Causes

1. Function Not Created

SQL

SELECT calculate_tax(100);  -- Function was never created

2. Wrong Argument Types

SQL

-- Function defined as: add_numbers(INTEGER, INTEGER)
SELECT add_numbers(1.5, 2.5);  -- Error: no function for NUMERIC
SELECT add_numbers(1, 2);      -- Works

3. Wrong Number of Arguments

SQL

-- Function takes 2 args
SELECT add_numbers(1);  -- Error: wrong number of args

4. Missing Extension

SQL

SELECT uuid_generate_v4();  -- Error: need uuid-ossp extension

5. Wrong Schema

SQL

SELECT myschema.my_function(1);  -- Function is in different schema

How to Fix It

Solution 1: Create the Function

SQL

CREATE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
  SELECT a + b;
$$ LANGUAGE SQL;

Solution 2: Cast Arguments

SQL

-- Cast to match expected types
SELECT add_numbers(1.5::INTEGER, 2.5::INTEGER);

-- Or use explicit type
SELECT add_numbers(CAST(1.5 AS INTEGER), CAST(2.5 AS INTEGER));

Solution 3: Install Extension

SQL

-- For UUID functions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v4();

-- For cryptographic functions
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
SELECT gen_random_uuid();

Solution 4: Check Function Signature

SQL

-- List functions by name
SELECT proname, pg_get_function_arguments(oid) AS args
FROM pg_proc
WHERE proname = 'add_numbers';

-- Or use \df in psql
\df add_numbers

Solution 5: Set Search Path

SQL

-- Include schema in search path
SET search_path TO myschema, public;

-- Or qualify function name
SELECT myschema.my_function(1);

Solution 6: Create Function with Multiple Signatures

SQL

-- Overload function for different types
CREATE FUNCTION add_numbers(a NUMERIC, b NUMERIC)
RETURNS NUMERIC AS $$
  SELECT a + b;
$$ LANGUAGE SQL;

-- Now both work
SELECT add_numbers(1, 2);        -- Uses INTEGER version
SELECT add_numbers(1.5, 2.5);    -- Uses NUMERIC version

Common Missing Functions

SQL

-- Random UUID
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
SELECT gen_random_uuid();

-- Levenshtein distance
CREATE EXTENSION IF NOT EXISTS "fuzzystrmatch";
SELECT levenshtein('hello', 'hallo');

-- Array aggregation (built-in)
SELECT array_agg(name) FROM users;

-- JSON aggregation (built-in)
SELECT json_agg(row_to_json(t)) FROM users t;

Best Practices

Check function exists before calling
Match argument types exactly
Create extensions in migrations
Document custom functions for the team
Use explicit type casts when needed