How to fix function does not exist in PostgreSQL

-- Check available functions matching the name
\df my_func    -- In psql
\df my_*       -- Pattern match

-- Or use the pg_proc system catalog
SELECT proname, pg_get_functiondef(oid)
FROM pg_proc
WHERE proname LIKE 'my_func%'
ORDER BY proname;

-- List all signatures for a function
SELECT prosignature
FROM pg_proc
WHERE proname = 'my_func';

-- Or more detailed view:
SELECT pg_get_functiondef(oid)
FROM pg_proc
WHERE proname = 'my_func'
ORDER BY oid DESC;

-- INCORRECT: integer passed to bigint function
SELECT my_func(42);

-- CORRECT: explicit cast to bigint
SELECT my_func(42::bigint);

-- Or cast in the definition when flexible
CREATE FUNCTION my_func(val bigint) ...

-- Original call with type mismatch
SELECT addnewuser(19, 'bbbb', 'aaaaa');
-- ERROR: function addnewuser(integer, unknown, unknown) does not exist

-- Fixed with explicit casts
SELECT addnewuser(19::integer, 'bbbb'::varchar, 'aaaaa'::varchar);

-- Or infer types from context
SELECT addnewuser(19, 'bbbb'::text, 'aaaaa'::text);

-- Check current search_path
SHOW search_path;
-- Example output: public, pg_catalog

-- If function is in "analytics" schema, call it explicitly
SELECT analytics.my_func(42);

-- Or add schema to search_path temporarily
SET search_path TO public, analytics, pg_catalog;
SELECT my_func(42);  -- Now resolves from analytics schema

-- Or set it for the session
ALTER ROLE current_user SET search_path TO public, analytics, pg_catalog;

SELECT schemaname, proname
FROM pg_proc
JOIN pg_namespace ON pg_proc.pronamespace = pg_namespace.oid
WHERE proname = 'my_func';

-- List installed extensions
\dx    -- In psql

-- Or query pg_extension
SELECT extname FROM pg_extension;

-- Common extensions and their functions:
-- uuid-ossp: uuid_generate_v4(), gen_random_uuid()
-- pg_trgm: similarity(), word_similarity()
-- hstore: each(), akeys(), vals()
-- intarray: icount(), sum()
-- json/jsonb: jsonb_pretty(), jsonb_array_elements()

-- Install a missing extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";
CREATE EXTENSION IF NOT EXISTS "hstore";

-- If CREATE EXTENSION fails, the extension may not be installed on the server
-- Contact your database administrator or ensure PostgreSQL contrib packages are installed

-- Check if you have execute permission
SELECT pg_has_role(current_user, 'postgres', 'MEMBER');

-- Grant execute permission (as superuser or function owner)
GRANT EXECUTE ON FUNCTION my_func(integer) TO public;
GRANT EXECUTE ON FUNCTION my_func(integer) TO user_role;

-- Check function ownership and permissions
SELECT proname, usename, proacl
FROM pg_proc
JOIN pg_user ON pg_proc.proowner = pg_user.usesysid
WHERE proname = 'my_func';

-- Example function definition
CREATE OR REPLACE FUNCTION my_func(val integer) RETURNS text AS $$
BEGIN
  RETURN 'Result: ' || val;
END;
$$ LANGUAGE plpgsql;

-- Test the function
SELECT my_func(42);

-- Or restore from a SQL dump
psql -d your_database < backup.sql

-- Check for errors during restore
psql -d your_database < backup.sql 2>&1 | grep -i error