postgrest icon indicating copy to clipboard operation
postgrest copied to clipboard

Add support for Stored Procedures

Open laurenceisla opened this issue 4 years ago • 2 comments

Resolves part of #1210. As noted in the issue, nested transactions won't be implemented

  • [x] Identify stored procedures in the schema cache
  • [ ] Query the data from the procedure (INOUT and OUT), use https://github.com/steve-chavez/postgrest/commit/8ac5a11b35a98685e288ced786f6377de8f90641 as reference
  • [ ] Add tests
  • [ ] Optimize code
  • [ ] Modify changelog

laurenceisla avatar Oct 08 '21 02:10 laurenceisla

We can fully support stored procedures transactions with the idea mentioned here.

steve-chavez avatar Jul 11 '22 18:07 steve-chavez

On 9.0.0, when trying to call a procedure, we used to fail with:

{
    "hint": "To call a procedure, use CALL.",
    "details": null,
    "code": "42809",
    "message": "api.do_nothing() is a procedure"
}

Now we fail with a schema cache error:

Could not find the api.do_nothing() function in the schema cache

I think both errors are imprecise, ideally we should throw a 501 Not Implemented and mention procedures are not supported yet. Also maybe suggest moving to a function since we already wrap it with a transaction.

steve-chavez avatar Jul 15 '22 21:07 steve-chavez