Calling Supabase Stored Procedures/Functions - Poor Documentation on Returned Data

[Katerlad]

Hello!

The Documentation on the Supabase site for Stored Procedures(Edit: This is talking about the documentation for stored procedures on the C# Library located here https://supabase.com/docs/reference/csharp/rpc) , is very lack luster. It would be great to have best practices for return data, as when calling a stored procedure it is often to return a table that will be used in your application code.

What is the best way to get attention to this? Is this discussion a good place? or should I create an Issue.

Also, In the meantime does anyone have examples of their best practice for syntax when returning data from a stored procedure?

Thanks,

[wiverson]

Are you talking about just vanilla SP inside of Postgres or calling SP via REST?

For vanilla you really don't need Supabase docs, you just need Postgres docs.

For SP via REST check out the PostgREST docs https://postgrest.org/en/v12/

For returning data I usually try to keep it simple, either a single "row" or a table. I believe you can get more fancy eg returning JSON with more irregular structures but I haven't needed to do that.

[wiverson]

Oh, you are basically right. Some of this stuff is documented via examples and tests in the PostgREST repo.

https://github.com/supabase-community/postgrest-csharp

Technically the response could be anything up to and including a simple string, depending on what you are sending back. So I think at some point the docs would wind up sort of recreating the PostgREST return type info...?

[Katerlad]

Yeah I think maybe it would be beneficial though to have the docs take the important snippets from the PostgREST return type info. If you look at other libraries docs, they have a little drop down for what to expect for the return type as well as a few more examples of calling a postgres function.

Also, Let me know what you think, but is there a reason that its sending back a "BaseResponse" and not a "ModeledResponse"?

I feel like when we call postgres function we should have the option to pass a generic type we are expecting for a model like

MyModel model = await supabase.Rpc< MyModel >("hello_world", null);

or

ModeledResponse < MyModel > response = await supabase.Rpc("hello_world", null);

[acupofjose]

@Katerlad it was originally coded to purposefully be more generic and leave it up to the developer to handle implementation. But, it may be time to add a helper/shortcut function for DX! Yes, the Content property on BaseResponse is the string content of whatever the Http request completes with.

So essentially, you're asking to add the following overloaded method, correct?

public async Task<TModeledResponse?> Rpc<TModeledResponse>(string procedureName, object? parameters = null)
{
  var response = await Rpc(procedureName, parameters);

  return string.IsNullOrEmpty(response.Content) ? default : JsonConvert.DeserializeObject<TModeledResponse>(response.Content!);
}

[Katerlad]

😍 @acupofjose Yeah that's basically it!

To be honest, I started this with just needing documentation to describe what the return object is, and what "Content" is exactly. I figured it out by digging into it manually, however it should be outlined in the documentation here,

But it turned into a request of how about we make an option to return a ModeledResponse with your model as the generic type! So thank you for the code above.

I made two issues to help improve this feature! Here they are one is for the ModeledResponse request like you made above, and the other is to track that documentation for this adds info about the return object and maybe the newly added modeledresponse feature if added :)

1. New ModeledResponse Feature - Return ModeledResponse for calling a Postgres Function/Stored Procedure #147
2. Documentation Improvement - Call a Postgres Function/Stored Procedure Documentation Improvement! #146

[acupofjose]

Sure thing - Thanks for the issues, I'll get to adding it in tomorrow! Apologies that you had to dig around for the functionality you were looking for!