Two more functions for REST API development using the JSON component

[stefanov-sm]

Discussed in https://github.com/lovasoa/SQLpage/discussions/315

^{Originally posted by **stefanov-sm** May 13, 2024} ### Motivation Build REST services that support `GET, POST, PUT, DELETE, ...` request methods and arbitrary length raw JSON input ### Suggestion - add two more functions - `sqlpage.request_method()` function that returns the request method as text. This would allow for code like ```sql select case sqlpage.request_method() when 'GET' then (...) when 'POST' then (...) when 'PUT' then (...) ... end; ``` - `sqlpage.request_body()` function that returns the request body payload as raw text. This would allow for code like (PostgreSQL) ```sql with t(j) as ( select jsonb_array_elements(sqlpage.request_body()::jsonb) ) ... ``` ### Expected result A fully functional REST services development toolset based on the JSON component.

[lovasoa]

0.21 is now live with sqlpage.request_method()

[lovasoa]

I think GitHub messed the formatting of your response... Can you open this thread directly on GitHub on the web and paste your message?

[stefanov-sm]

Great!
Here is a REST service template (working) that might be useful for the documentation, in two flavours -

• using separate method implementation files (file "Using run_sql.sql")
• using stored functions (file "Using stored functions.sql").

Method implementation queries and stored functions are purely illustrative and serve no particular purpose.

Tested successfully using curl -X POST -i http://localhost:83/svc/ --data "one=1&two=2&three=3" curl -X GET -i "http://localhost:83/svc/?one=1&two=2&three=3"

Both run under PostgreSQL. For versions older than PG14 use JSONB arrow syntax instead of subscripting. Function sqlpage.variables('post') may be replaced by sqlpage.request_body() in the future.

REST template.zip