The Error
You call a stored function and PostgreSQL throws:
ERROR: function send_welcome_email(unknown) does not exist
LINE 1: SELECT send_welcome_email('user@example.com');
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
Frustrating, because you know the function exists. You wrote it. You can see it in pgAdmin right now. Yet the moment you call it from your app, a different role, or a fresh psql session — PostgreSQL stares back blankly.
Why This Happens
When you call a function, PostgreSQL does not search the entire database. It checks schemas in a specific order defined by search_path. By default that order is "$user", public — meaning it looks in the schema named after the current user, then public, and nowhere else.
Put your function in a custom schema like app or myschema and forget to add that schema to search_path, and PostgreSQL genuinely cannot find it. The error is accurate, even if it feels wrong.
How does this sneak up on you? A few common ways:
- The function was created inside a non-
publicschema (often the case with ORMs and migration tools) - Flyway, Liquibase, or Alembic ran
SET search_pathduring the migration but your app connects without it - Your application role has a different default
search_paththan the admin account you used to create the function - A PostgreSQL major-version upgrade silently reset role-level search path settings
Step 1 — Find Where the Function Actually Lives
Before fixing anything, confirm where the function is. Open psql and run:
SELECT n.nspname AS schema, p.proname AS function_name
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE p.proname = 'send_welcome_email';
Example output:
schema | function_name
--------+--------------------
app | send_welcome_email
(1 row)
There it is — schema app, not public. That single fact explains everything.
Step 2 — Check the Current search_path
SHOW search_path;
search_path
-----------------
"$user", public
(1 row)
app is not listed. Every unqualified call to send_welcome_email() will fail until that changes.
Step 3 — Choose Your Fix
Option A: Schema-qualify the function call (quickest, zero config)
Prefix every call with the schema name:
SELECT app.send_welcome_email('user@example.com');
No database changes required. Great for one-off scripts or when the function is called in only two or three places. Gets tedious fast if you call it everywhere.
Option B: Set search_path for the current session
Add the schema just for your connection:
SET search_path TO app, public;
SELECT send_welcome_email('user@example.com');
Resets when the session ends. Other connections are untouched — safe for testing and debugging without side effects.
Option C: Set search_path permanently for a role (recommended for apps)
When your application always connects as the same role, this is the cleanest solution:
ALTER ROLE app_user SET search_path TO app, public;
Every new connection by app_user picks up app automatically. Existing sessions are not affected — restart your app or reconnect to see it kick in.
Option D: Set search_path at the database level
All roles on this database need the same schema order? Set it once at the database level:
ALTER DATABASE mydb SET search_path TO app, public;
Role-level settings take priority over this, so it will not override Option C. It is the broadest lever available — use it carefully on shared databases.
Option E: Bake search_path into the function itself (most portable)
PostgreSQL lets you pin a search_path directly in the function definition. The function then resolves names relative to its own schema, no matter who calls it or what their session looks like:
ALTER FUNCTION app.send_welcome_email(text)
SET search_path = app, public;
Or set it at creation time:
CREATE OR REPLACE FUNCTION app.send_welcome_email(email text)
RETURNS void
LANGUAGE plpgsql
SET search_path = app, public
AS $$
BEGIN
-- function body
END;
$$;
This is the hardest to forget and the easiest to reason about. It also blocks schema-injection attacks where a malicious schema is prepended to a superuser's search path. Worth the extra line.
Step 4 — Verify the Fix
After applying your chosen option, run a quick sanity check:
SELECT send_welcome_email('test@example.com');
Used Option C or D? Open a brand-new connection first — those changes only apply to sessions started after the ALTER.
To confirm the role's effective search path after ALTER ROLE:
-- Reconnect as app_user, then:
SHOW search_path;
search_path
--------------
app, public
(1 row)
That output means the role is configured correctly and new connections will find the function without any extra setup.
Fixing This Inside Your Application
When the error comes from application code — Node.js, Python, Java — the cleanest approach is a connection hook that runs SET search_path immediately after every new connection is established.
Node.js (node-postgres):
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
pool.on('connect', (client) => {
client.query("SET search_path TO app, public");
});
Python (psycopg2):
import psycopg2
conn = psycopg2.connect(dsn)
conn.cursor().execute("SET search_path TO app, public")
conn.commit()
SQLAlchemy (Python):
from sqlalchemy import event, text
@event.listens_for(engine, 'connect')
def set_search_path(dbapi_connection, connection_record):
cursor = dbapi_connection.cursor()
cursor.execute("SET search_path TO app, public")
cursor.close()
Bonus: Audit All Functions and Their Schemas
Not sure how many functions might be hiding in non-public schemas? This query lists every user-defined function alongside its schema and signature:
SELECT n.nspname AS schema,
p.proname AS function,
pg_get_function_identity_arguments(p.oid) AS arguments
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY n.nspname, p.proname;
Anything outside public is a candidate for this error if callers do not include that schema in their search_path. Run this after a migration or a major version upgrade — it takes two seconds and has caught many surprises.
Quick Reference
- One-off fix:
schema.function_name()— no config needed - Single session:
SET search_path TO schema, public - Per role (best for apps):
ALTER ROLE role SET search_path TO schema, public - Per database:
ALTER DATABASE db SET search_path TO schema, public - Per function (most portable):
ALTER FUNCTION ... SET search_path = schema, public