Using Supabase DB functions to return manipulated data records

currently, i create DB functions to do stuff like table joins, sums, counts, etc. It would be really nice to be able to set a collection from a db function. currently i have create a WeWeb variable.

i then run some javascript in a global workflow like this:

async function fetchData() {
  try {
    let { data, error } = await wwLib.wwPlugins.supabase.instance.rpc('unique_domains')
    wwLib.wwVariable.updateValue('c00d9d8d-d985-4e56-8cfa-e4a02b13fd10',data)
  } catch (error) {
    console.error(error);
  }
}

// Call the async function
fetchData();

this calls the function then stores the results in a variable. it would be nice to be able to call this from the plugin.

2 Likes

Great feedback! Shared it with the integration squad so they can explore further :slight_smile:

2 Likes

Check this out

you can do joins and probably also some other functions, it is very badly documented both on WeWeb’s and Supabase’s side, but with some tinkering, I’ve managed to get my joins done via the native WeWeb Supabase integration without any code. This way you might not need to do rpc’s at all, or less of them.

1 Like

Also, you can actually create a collection out of PostgreSQL functions in Supabase, all you need to do is call it via the REST API. Then you can use it as you’d use a normal collection.
You can pass any arguments in the body (Fields). The only disadvantage of this is that you have to sacrifice one plugin slot (out of the little 4 you probably have) to do maybe like 2 queries in my case, so yeah, if the native Supabase DB Weweb connector had this, it would be a nice addition.

2 Likes

:exploding_head: I had no clue I could call my db functions like that!!!

Sometimes, even though I’m a hater of ChatGPT, you can ask it some things that docs have hidden very very deep inside :slight_smile: You can’t use auth in PostgreSQL functions, so I’d still go with this way if you need RLS

Also, Supabase runs on PostgREST, so if you have the brains to do it (I don’t have them yet), you can learn all sorts of hot stuff on their site, eg.
https://postgrest.org/en/stable/references/api/stored_procedures.html#stored-procedures

1 Like

Is that also what chatGPt told you? You can definitely use rls in supabases db functions. You need to throw the users access token as an authorization header with the request.

If you use the plugin instance and execute the code from above, you can watch the call in the network traffic. It should be adding the users access token on the request