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

Postgresql – 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).

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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

  2. 0

    I don’t think the example you showed fully reflects your actual config, because it just doesn’t work:

    • The text variant defines a json_item variable but then tries to use a not-yet defined json_data. Test1 here.
    • The ::jsonb in the text variant looks like you tried to redirect to the jsonb function, but since the result of that cast is saved to a variable defined as json_item json, it actually redirects to the json variant instead.
    • The jsonb variant is broken because it tries to use json_array_elements on a jsonb. Test2 here.
    • You’re overlooking schema qualification by creating the test_table in vehicle_database schema, but then referring to it from the function bodies as test_table without updating the search_path first or using the fully schema-qualified identifier. Test3 here.

    Fixing the 3 things above leads to a setup where that exact set of overloaded functions works fine: Test4 here.
    If you call update_test_table() with a single-quoted argument, without casting it, you’ll get the text variant because that’s the pg_type.typispreferred type. If you only set up json and jsonb variants (or only those two are visible), neither of them is preferred in their type group, which is why you’d get the error
    (Test5 here)

    ERROR:  function update_test_table(unknown) is not unique
LINE 2: select update_test_table('[{"data":"4","test_type":"RED"}]')...
               ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.

    …unless you used explicit type casting as suggested by @Adrian Klaver.

    My bet is you need to make sure all of those functions are visible in your search path and/or you use full, schema-qualified function and table names in your code.
    You might want to take a look at Chapter 10. Type Conversion and 5.9.3. The Schema Search Path.

    On the PostgREST side, it seems that they want you to always name and wrap your arguments in a json:

    Calling with POST
    –
    To supply arguments in an API call, include a JSON object in the request payload. Each key/value of the object will become an argument.

    For instance, assume we have created this function in the database.

    CREATE FUNCTION add_them(a integer, b integer)
RETURNS integer AS $$
SELECT a + b;
$$ LANGUAGE SQL IMMUTABLE;

    The client can call it by posting an object like

    curl "http://localhost:3000/rpc/add_them" 
 -X POST -H "Content-Type: application/json" 
 -d '{ "a": 1, "b": 2 }'

    Which explains why your idea works:

    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

    But only if you make sure the function is visible and if overloaded, Postgres knows a clear type conversion path to pick one.

    If you don’t like having to wrap your parameters, you can ditch their naming both in the call as well as in the function signature: Test6

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

    Quoting PostgREST doc again:

    Functions with a single unnamed JSON parameter
    –
    If you want the JSON request body to be sent as a single argument, you can create a function with a single unnamed json or jsonb parameter. For this the Content-Type: application/json header must be included in the request.

    CREATE FUNCTION mult_them(json) RETURNS int AS $$
 SELECT ($1->>'x')::int * ($1->>'y')::int
$$ LANGUAGE SQL;

    curl "http://localhost:3000/rpc/mult_them" 
 -X POST -H "Content-Type: application/json" 
 -d '{ "x": 4, "y": 2 }'

    PostgREST also offers a bulk update, which technically does "update several rows at a time":

    Update
    –
    To update a row or rows in a table, use the PATCH verb. Use Horizontal Filtering to specify which record(s) to update. Here is an example query setting the category column to child for all people below a certain age.

    curl "http://localhost:3000/people?age=lt.13" 
 -X PATCH -H "Content-Type: application/json" 
 -d '{ "category": "child" }'

    Which means that, following your example, you could

    curl "http://127.0.0.1:3000/rpc/update_test_table?test_type=eq.RED" 
     -X PATCH 
     -H "Authorization: Bearer $TOKEN" 
     -H "Content-Type: application/json" 
     -d '{"test_data":"4"}'

    And all REDs would get a 4 in their test_data. I’m guessing that in reality, you’re after something like

    curl "http://127.0.0.1:3000/rpc/update_test_table?test_type=in.(RED,GREEN,BLUE)"

    where you can set test_data to something else if test_type is RED than when it’s GREEN or BLUE, in which case your function method is necessary.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search