PostgREST call to function where either an insert or update will be done

[MyProxyPass]

Hello -- I'm exploring the Postgres Tembo.io platform which enables this awesome extension. I'm looking to use a function to either insert or update a row but having some difficulty while prototyping. As an example, I've included my Python request, the error I'm receiving and my POC function. I'd appreciate any thoughts one has resolving. Also note -- on a different thread I mentioned why I think a stored procedure would be ideal but if this would work then that should suffice most of my use case (although I could see data engineering routines more complex that would be initiated by the PostgREST API call).

import requests

resp = requests.post(
    url=f"<my_org>/restapi/v1/rpc/f_car_inventory?param_make=honda&param_model=pilot&param_yr=2010&param_msrp=21000",
    headers={"Authorization": f"<token>" },
)
print(resp.json())

{'code': 'PGRST104', 'details': 'Failed to parse [("param_make","honda"),("param_model","pilot"),("param_yr","2010"),("param_msrp","21000")]', 'hint': None, 'message': 'Unexpected param or filter missing operator'}

CREATE OR REPLACE FUNCTION public.f_car_inventory(
    param_make text,
    param_model text,
    param_yr int,
    param_msrp numeric
)
RETURNS VOID AS
$$
BEGIN

	INSERT INTO public.car_inventory (make, model, yr, msrp)
	VALUES (param_make, param_model, param_yr, param_msrp)
	ON CONFLICT (make, model, yr)
	DO UPDATE
	SET msrp = param_msrp;
	
END;
$$
LANGUAGE plpgsql;

[laurenceisla]

Hi! Don't know if you checked already, but PostgREST can handle upserts by default with the POST and PUT methods. It does something similar to what your SQL query is doing.

If you still need an RPC (e.g. for more complex queries) then you can use a function. But, in a POST request, the key/values should be sent in JSON format inside the body of the request not as query parameters. So this: ?param_make=honda&param_model=pilot&param_yr=2010&param_msrp=21000 from url="..." should go in json={...} instead. See the Functions section in the docs for more info.

[MyProxyPass]

Thank you so much for the helpful nudge. I was able to get my call to work as needed with the following code:

import requests
import json

url = f"https://<org>/restapi/v1/rpc/f_car_inventory"
headers = {"Content-Type": "application/json", "Authorization": <token>}
data = {"param_make":"honda", "param_model":"pilot", "param_yr":"2010", "param_msrp":"27000"}
json_data = json.dumps(data)

resp = requests.post(url, headers=headers, data=json_data)

I'm new to PostgREST but super excited about it! Thank you again!