NpgsqlRest

Appearance

Comments

HTTP ​

Expose a PostgreSQL function, procedure, or SQL file as an HTTP endpoint.

Keywords ​

http

Syntax ​

code
HTTP
HTTP <method>
HTTP <path>
HTTP <method> <path>

method: GET, POST, PUT, DELETE, QUERY

QUERY is the recently standardized safe, idempotent HTTP method that carries the query in the request body: parameters default to the JSON body (like POST), while clients treat it as a cacheable read (the TypeScript client's React Query hooks map it to useQuery, MCP tools get readOnlyHint: true). Available since version 3.20.0. Note: QUERY endpoints are not representable in OpenAPI 3.0/3.1 documents and are skipped there with a logged warning.

path: Custom URL path (must start with / or be a relative path)

CommentsMode Requirement ​

The HTTP annotation behavior depends on the CommentsMode configuration setting:

ModeHTTP Annotation Behavior
OnlyAnnotatedRequired - Endpoints are only created for routines with HTTP in their comment (or a plugin annotation that requests an endpoint, such as @mcp). Default. OnlyWithHttpTag is a backward-compatible alias.
ParseAllOptional - All routines become endpoints; HTTP can customize method/path.
IgnoreIgnored - All routines become endpoints; comments are not parsed.

With the default OnlyAnnotated mode, a function without the HTTP annotation will not be exposed as an endpoint.

Default Behavior ​

When method is not specified:

  • GET for non-volatile functions, or names starting with get_, containing _get_, or ending with _get
  • POST for all other functions

When path is not specified, it's generated from the function name using the configured URL prefix and naming conventions.

Examples ​

Basic Endpoint ​

sql
sql
create function get_status()
returns text
language sql
begin atomic;
select 'OK';
end;

comment on function get_status() is 'HTTP';

Equivalent as a SQL file endpoint (sql/get-status.sql):

sql
sql
-- HTTP
select 'OK';

Creates: GET /api/get-status

Explicit HTTP Method ​

sql
sql
create function create_user(_name text)
returns int
language sql
begin atomic;
insert into users(name) values(_name) returning id;
end;

comment on function create_user(text) is 'HTTP POST';

Creates: POST /api/create-user

Custom Path ​

sql
sql
create function get_all_users()
returns setof users
language sql
begin atomic;
select * from users;
end;

comment on function get_all_users() is 'HTTP GET /users';

Creates: GET /users

Method and Custom Path ​

sql
sql
create function search_products(_query text)
returns setof products
language sql
begin atomic;
select * from products where name ilike '%' || _query || '%';
end;

comment on function search_products(text) is 'HTTP GET /products/search';

Creates: GET /products/search

Multi-line with Documentation ​

sql
sql
comment on function get_user_profile(int) is
'Returns the complete user profile including preferences.
Used by the frontend dashboard.

HTTP GET /users/profile';

The documentation text is ignored; only the HTTP line is parsed.

Unrecognized Method Becomes Path ​

sql
sql
comment on function my_endpoint() is 'HTTP custom-endpoint';

Since custom-endpoint is not a valid HTTP method, it's treated as a path:

Creates: POST /custom-endpoint

Path Parameters ​

You can define RESTful path parameters using the {param} syntax in URL paths. Parameter values are extracted directly from the URL path instead of query strings or request body.

Single Path Parameter ​

sql
sql
create function get_product(p_id int)
returns text
language sql
begin atomic;
select ...;
end;

comment on function get_product(int) is 'HTTP GET /products/{p_id}';

Call: GET /products/123 → p_id = 123

Multiple Path Parameters ​

sql
sql
create function get_review(p_id int, review_id int)
returns text
language sql
begin atomic;
select ...;
end;

comment on function get_review(int, int) is 'HTTP GET /products/{p_id}/reviews/{review_id}';

Call: GET /products/5/reviews/10 → p_id = 5, review_id = 10

Path Parameters with Query String ​

sql
sql
create function get_product_details(p_id int, include_reviews boolean default false)
returns text
language sql
begin atomic;
select ...;
end;

comment on function get_product_details(int, boolean) is 'HTTP GET /products/{p_id}/details';

Call: GET /products/42/details?includeReviews=true → p_id = 42, include_reviews = true

Path Parameters with JSON Body ​

sql
sql
create function update_product(p_id int, new_name text)
returns text
language sql
begin atomic;
select ...;
end;

comment on function update_product(int, text) is 'HTTP POST /products/{p_id}';

Call: POST /products/7 with body {"newName": "New Name"} → p_id = 7, new_name = "New Name"

Path Parameter Key Features ​

  • Parameter names in {param} can use either the PostgreSQL name ({p_id}) or the converted camelCase name ({pId}), matching is case-insensitive
  • Works with all HTTP methods (GET, POST, PUT, DELETE, QUERY)
  • Can be combined with query string parameters (GET/DELETE) or JSON body parameters (POST/PUT/QUERY)
  • Supports all parameter types (int, text, uuid, bigint, etc.)
  • Zero performance impact on endpoints without path parameters

Comments