Function overloading vs No Function Matches problem

I am needing to update several rows at a time through the Rest API for Postgres. The only way I have figured out how to do this is through a function call, where I pass the JSON to the function and iterate over the JSON and perform the updates.

However, depending on what I do, I am having one of two problems.

• If I create generic function that takes in a type json or jsonb, PostgREST tells me:

  No function matches the given name and argument types. You might need to add explicit type casts.
  

• But then, if I create an overloaded function where I accept a json or a jsonb object, I get the error:

  {"code":"PGRST203","details":null,"hint":"Try renaming the parameters or the function itself in the database so function overloading can be resolved","message":"Could not choose the best candidate function..."
  

How do I get around this issue? PostgREST can't find the function without the overloading, but when overloaded, it can find the function but doesn't know which one to use.

SQL:

-- TABLE --
CREATE TABLE IF NOT EXISTS vehicle_database.test_table(
    test_table_id BIGSERIAL PRIMARY KEY,
    test_data INTEGER,
    test_type VARCHAR(8)
);

-- Overloaded function using json --
CREATE OR REPLACE FUNCTION update_test_table(p_json json)
RETURNS void AS $$
DECLARE
    json_item json;
BEGIN
    FOR json_item IN SELECT json_array_elements(p_json) LOOP
        PERFORM update_test_table_row(
            (json_item->>'data')::integer,
            (json_item->>'test_type')::text);
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Overloaded function using jsonb --
CREATE OR REPLACE FUNCTION update_test_table(p_json jsonb)
RETURNS void AS $$
DECLARE
    json_item json;
BEGIN
    FOR json_item IN SELECT json_array_elements(p_json) LOOP
        PERFORM update_test_table_row(
            (json_item->>'data')::integer,
            (json_item->>'test_type')::text);
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Overloaded function using text --
CREATE OR REPLACE FUNCTION update_test_table(p_json text)
RETURNS void AS $$
DECLARE
    json_item json;
BEGIN
    json_data = p_json::JSONB;
    PERFORM update_test_table(json_data);
END;
$$ LANGUAGE plpgsql;

-- Update the row
CREATE OR REPLACE FUNCTION update_test_table_row(p_data integer, p_type text)
RETURNS void AS $$
BEGIN
    UPDATE test_table SET test_data = p_data WHERE test_type = p_type;

    IF NOT FOUND THEN
        RAISE NOTICE 'No rows updated for data: %, type: %', p_data, p_type;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Permissions (same kind of users as in the tutorials)
GRANT EXECUTE ON FUNCTION update_test_table(p_json json) TO web_insert;
GRANT EXECUTE ON FUNCTION update_test_table(p_json jsonb) TO web_insert;
GRANT EXECUTE ON FUNCTION update_test_table(p_json text) TO web_insert;
GRANT EXECUTE ON FUNCTION update_test_table_row(p_data integer, p_type text) TO web_insert;

Here is how I call it:

curl -X POST -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" -d '[{"test_data":"4","test_type":"RED"}]' http://127.0.0.1:3000/rpc/update_test_table

$TOKEN is my private token that is used with PostgREST in the jwt-secret field.

Unfortunately, PostgREST has no verbose logging, and the Postgres logs don't really help with this. Postgres tells me the function does not exist, but the Postgres logs don't actually print the JSON parameters (it prints all of the other curl parameters, but not what is included with the -d).

Found the answer.

This is not located in the PostgREST documentation, and it should be included.

If you are attempting to call an RPC function from the PostgREST API, passing JSON data or JSONB data, you have to assign that data to a variable.

So, in the instance of my problem listed above, get rid of the overloaded function with the text argument, and then get rid of one of the other overloaded functions (either the json or jsonb arguments).

Then your curl function needs to assign the JSON to a variable name that matches the name in the function.

So my solution (after removing 2 overloaded functions is):

curl -X POST -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" \
  -d '{"p_json":[{"test_data":"4","test_type":"RED"}]}' \
  http://127.0.0.1:3000/rpc/update_test_table