Search code examples
sqljsonpostgresqlpostmanpostgrest

Json parameter in POSTMAN with postgreSQL function

I have created the postgreSQL function below:

create or replace function analyse_lat(jsonvariable json) returns table (ok boolean, nb text) as 
$BODY$
declare 
    r record;
begin
    for r in (select * from json_to_recordset(jsonvariable->'list') as foo(id int, price1 numeric, price2 numeric)) loop
        --- DO THINGS
        ok:=(r.price1>r.price2);
        nb:='this is text returned';
        return next;
    end loop;
end;
$BODY$
language plpgsql;

it takes its format as input :

{
     "users":"John",
     "Level":"1",
     "list":[
      {"id":"1", "price1":"100.50", "price2":"90.50"},
      {"id":"2", "price1":"100.60", "price2":"80.50"},
      {"id":"2", "price1":"105.50", "price2":"700.50"}
    ]
}

However I can't test with POSTMAN he interprets this as text and not JSON. do you have any ideas?

enter image description here

error message :

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

Thank you.

Solution

  • The problem is type casting. The function expects parameters of types INT and NUMERIC. You are passing these parameters to JSON as strings. Try changing the request body.

    {
     "users":"John",
     "Level":"1",
     "list":[
      {"id":1, "price1":100.50, "price2":90.50},
      {"id":2, "price1":100.60, "price2":80.50},
      {"id":2, "price1":105.50, "price2":700.50}
    ]
}