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

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)

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