Error Calling JSONB-Parameter Function in PostgreSQL: "function public.upsert_customer() does not exist"

[africab2c]

Hello,

I am facing an issue with a PostgreSQL function call in my application. Despite the function upsert_customer being defined in my database and working correctly when called directly within a DO block in Supabase, I keep encountering an error when calling it through my API.

Error Message:

{
    "code": "42883",
    "details": null,
    "hint": "No function matches the given name and argument types. You might need to add explicit type casts.",
    "message": "function public.upsert_customer() does not exist"
}

Function Definition:

CREATE OR REPLACE FUNCTION public.upsert_customer(params JSONB) RETURNS INT AS $$
DECLARE
    v_existing_customer_id INT;
BEGIN
    v_existing_customer_id := customer_exists(
        params->>'email_param',
        (params->>'vendor_id_param')::int,
        params->>'vendor_customer_id_param',
        (params->>'country_gid_param')::int
    );

    RAISE NOTICE 'Checking existence, customerExists returned: %', v_existing_customer_id;

    IF v_existing_customer_id = -1 THEN
        RAISE NOTICE 'Customer does not exist, inserting new customer.';
        v_existing_customer_id := insert_customer(params);
        RAISE NOTICE 'Customer with ID % inserted successfully.', v_existing_customer_id;
    ELSE
        RAISE NOTICE 'Customer exists, attempting to update customer ID: %', v_existing_customer_id;
        params := params || jsonb_build_object('customer_id', v_existing_customer_id);
        v_existing_customer_id := updateCustomer(params);
        RAISE NOTICE 'Customer with ID % updated successfully.', v_existing_customer_id;
    END IF;

    RETURN v_existing_customer_id;
END;
$$ LANGUAGE plpgsql;

Function Call:
The function call works when executed directly in Supabase using:

DO $$ 
DECLARE
    result INT;
BEGIN
    result := upsert_customer('{"middle_name_param":"Riz","email_param":"k1hre5rebsex@sxb1plzcpnl504478.prod.sxb1.secureserver.net","country_gid_param":2245662,"phone_param":"779108818","vendor_id_param":1,"first_name_param":"Riz","last_name_param":"wan","currency_param":"XOF","vendor_customer_id_param":"v457"}'::jsonb);
    RAISE NOTICE 'Result: %', result;
END $$;

However, when I try to invoke this from my API (using a JSON payload in a POST request via Postman or from a java Azure Function), I receive the error that the function does not exist, or "empty or invalid json".

Questions:

1. Why might PostgreSQL not recognize the function from the API call, even though it exists and works within Supabase?
2. How should I correctly format the API call or modify the function to ensure it is recognized when called from outside of Supabase?

Any insights or suggestions would be greatly appreciated. Thank you for your help!

[GaryAustin1]

You don't show your rpc call or Postman? The error is either the name or the types of parameters do not match.
You do have the parameter named so you would need to use the name in your call to {"params":{json}}

[GaryAustin1]

It also appears there is another option for a single json parameter with a header...
https://postgrest.org/en/v12/references/api/stored_procedures.html#functions-with-a-single-json-parameter

[africab2c]

thanks @GaryAustin1 , using the name in my call to {"params":{json}} solved the problem.