postgREST route using function does not update table

Question

I am using Supabase which has a built in postgREST server. In this server I have created a function that should update a value given a checksum:

CREATE OR REPLACE FUNCTION set_value(_id TEXT, _value INTEGER, _check INTEGER) RETURNS BOOLEAN AS $$
BEGIN
  IF get_checksum(_value) = _check THEN
    UPDATE values_table SET score = _value WHERE device_id = _id;
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;
END;
$$ LANGUAGE plpgsql;

-- the schema is reloading after creating a function according to the docs
NOTIFY pgrst, 'reload schema';

When I call this function via sql everything works cheerio and I get the TRUE response, and I can see the value is updated in the table

SELECT * FROM set_value('SOME_ID', 123, 123456)
-- | Results |
-- | true    |

However, calling this via api does not seem to work. I get the correct responses (true when checksum matches, false otherwise), but the value in the table remains unchanged.

I am using the POST request below, which according to the documentation, should run in a read/write transaction.

curl --location --request POST 'https://<myapp>.supabase.co/rest/v1/rpc/set_value' \
--header 'Authorization: Bearer <mytoken>' \
--header 'apiKey: <mykey>' \
--header 'Content-Type: application/json' \
--data-raw '{
    "_id": "_deviceId",
    "_value": 123,
    "_check": 123456
}'

What am I doing wrong?

I couldn't reproduce with the example you gave (without using `get_checksum`). I suggest you try to debug by simplifying the function, doing first an UPDATE and then adding the conditionals to see where it fails exactly. And the `set_value` function returns `BOOL` right? It says `TRUE` in the example. — Laurence Isla, Jul 21 '22 at 23:11
@LaurenceIsla yes, it returns a bool, that was a typo. I will give a shot at simplifying — Felipe, Jul 21 '22 at 23:15
I cannot find anyone with this problem. I created a new function with no no checksum, no variables, no conditional, nothing but a simple update, and despite receiving the correct return value, it simply does nothing. It could be permissions or the request itself? — Felipe, Jul 22 '22 at 13:48

score 1 · Answer 1 · answered Jul 25 '22 at 18:15

You will need to add volatile to the function creation in order for it to perform an update on the table.

CREATE OR REPLACE FUNCTION set_value(_id TEXT, _value INTEGER, _check INTEGER) RETURNS BOOLEAN AS $$
BEGIN
  IF get_checksum(_value) = _check THEN
    UPDATE values_table SET score = _value WHERE device_id = _id;
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;
END;
$$ LANGUAGE plpgsql volatile;

You can read more about the volatile keyword in this other SO answer How do IMMUTABLE, STABLE and VOLATILE keywords effect behaviour of function?

After some investigation, I am not 100% yet if it is because of `VOLATILE` but I had to add `SECURITY DEFINER` as well. I will confirm once I have fully tested. Related: https://github.com/PostgREST/postgrest/discussions/2387 — Felipe, Aug 05 '22 at 21:51

postgREST route using function does not update table

1 Answers1