Problem with PostgreSQL Function Behavior: REST API via WeWeb vs. Direct Call via Supabase SQL Editor

[kanepaamauloa]

High-Level Function Description

I have a PostgreSQL function designed to maintain a list of associations between entities. The function updates the associations by:

1. Removing existing associations that are not in the provided list.
2. Adding new associations from the provided list that do not already exist.

The Problem

When I call this function directly from the Supabase SQL console, it works as expected:

• Associations that are not in the provided list are correctly removed.
• New associations from the provided list are correctly added.

However, when I call the same function via the Supabase REST API, via WeWeb Supabase - Call a Postgres function, the behavior is inconsistent:

• Associations that should be removed are not deleted from the database.
• The function returns the outdated list of associations, including those that should have been removed.

Example

Original Associations

entity_a_id	entity_b_id
11	37
11	67
11	95
11	129

update_associations(11, ARRAY[67, 95, 129]);

Expected result: the first row should be removed from the database.

• Direct Call (Supabase SQL Editor):
  • Works correctly, associations are updated as expected. Row [11, 37] is removed.
• REST API Call via WeWeb Supabase - Call a Postgres function:
  • Does not remove associations correctly, and the returned results include associations that should have been deleted.

[Broberto]

Might this be RLS? Sounds to me like it works with postgres superuser, but not with authorized role

[kanepaamauloa]

I have these two policies on the table.

[kanepaamauloa]

Needed to add a policy for DELETE as well…

D’oh!