CODEWITHSUNDEEP
postgresqlsupabasepostgrest
Felipe
Felipe

Reputation: 12008

postgREST route using function does not update table

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?

Upvotes: 1

Views: 211

Answers (1)

Andrew Smith
Andrew Smith

Reputation: 1841

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?

Upvotes: 1

Related Questions