Question 306886 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL Trigger Function Call Error: "function <function> doesn't exist"

I have a PostgreSQL trigger that fires after an INSERT on the auth.users table, calling a function handle_new_user() which, in turn, calls insert_into_profiles() to add user data to another table.

The insert_into_profiles function is defined as follows:

CREATE OR REPLACE FUNCTION insert_into_profiles(
    inp_profile_id UUID,
    inp_first_name TEXT,
    inp_last_name TEXT
)
RETURNS VOID AS $$
BEGIN
    INSERT INTO profiles (profile_id, first_name, last_name)
    VALUES (inp_profile_id, inp_first_name, inp_last_name);
END;
$$ LANGUAGE plpgsql;

The trigger function handle_new_user() is:

CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
    PERFORM insert_into_profiles(
        NEW.id,
        (NEW.raw_user_meta_data->>'first_name'),
        (NEW.raw_user_meta_data->>'last_name')      
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER on_auth_user_created
AFTER INSERT ON auth.users
FOR EACH ROW
EXECUTE FUNCTION handle_new_user();

However, when the trigger fires, I encounter an error:

function insert_into_profiles(uuid, text, text) doesn’t exist.

What could be causing this issue?

The function works fine when manually called e.g. SELECT insert_into_profiles('12345678-1234-5678-1234-1234567890123,'Jhon','Doe');, but within the trigger context, it seems to be unable to find the function signature.

Any insights or suggestions on how to resolve this error would be greatly appreciated. Thank you!

Tags:

2

Answers


  1. 0

    The function probably is not on your search_path. You should always set an appropriate search_path on each function, so that all the tables and functions that you call in the function are in one of the schemas. For example

    ALTER FUNCTION handle_new_user SET search_path = auth, public;

    That will work if insert_into_profiles() is in schema auth or in schema public.

    Login or Signup to reply.
  2. 0

    Make sure your functions are set to security definer

    CREATE OR REPLACE FUNCTION insert_into_profiles(
    inp_profile_id UUID,
    inp_first_name TEXT,
    inp_last_name TEXT
)
RETURNS VOID
security definer
AS $$
BEGIN
    INSERT INTO profiles (profile_id, first_name, last_name)
    VALUES (inp_profile_id, inp_first_name, inp_last_name);
END;
$$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS TRIGGER
security definer
AS $$
BEGIN
    PERFORM insert_into_profiles(
        NEW.id,
        (NEW.raw_user_meta_data->>'first_name'),
        (NEW.raw_user_meta_data->>'last_name')      
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search