Allow option to only generate certain HTTP methods for a DB view

[ghost]

Description of issue

As discussed earlier in the Gitter, it would be nice if there would be an option to avoid generation of HTTP methods for certain views.

Example:

If you have 1 table which is called src.orders and two views on it which are named api.orders_per_hour and api.orders_per_day. It would be a nice addition if there would be an option to only allow certain HTTP methods (e.g. only GET). So that it's clear for the users of the API that certain methods are not to be used at all. This especially applies if you communicate the API spec with something such as Swagger.

(Expected behavior vs actual behavior)

Currently when a view is created, PostgREST automatically generates a GET, POST, DELETE, and PATCH. In cases where the view contains a group by it does seem to only create a GET.

[wolfgangwalther]

I think this currently depends on whether the view is updateable or not:

postgrest/src/PostgREST/App.hs

Line 279 in 302d4e1

let allowH = (hAllow, if tableInsertable table then "GET,POST,PATCH,DELETE" else "GET")

A view is updateable if either you have some triggers defined for that or it is auto-updateable. See the postgres docs for when that is the case - the GROUP BY is one of the cases where it's not.

So your view seems to be auto-updateable right now. The question then is: How is it possible to prevent auto-updateability?

It seems like a view is not updateable anymore when it contains the OFFSET clause. The docs for that say:

OFFSET 0 is the same as omitting the OFFSET clause, as is OFFSET with a NULL argument.

So maybe you can just append OFFSET NULL to your view definition. The view should be the same, but it should only have GET in the api output.

[ghost]

Thanks for the suggestion @wolfgangwalther. I've tested your suggestion but it looks as the methods are still being generated.

Example query:

create view api.xyz as
  select
    a,
    b,
    c
  from
    public.test;
  offset null

Switching to a MATERIALIZED VIEW does seem to only generate a GET. However in this case we would manually need to refresh the VIEW

[ghost]

@wolfgangwalther never mind. I was misreading the docs you suggested.

The view definition must not contain WITH, DISTINCT, GROUP BY, HAVING, LIMIT, or OFFSET clauses at the top level.

In my example the offset null is not at the top level. It does work. Thanks for the suggestion. Will definitely use this.

[wolfgangwalther]

This "hack" seems like something that we could document somewhere.

[wolfgangwalther]

While the hack to prevent auto-updateability is one thing, looking at the code for tableInsertable (and the line in my comment above), it seems like the the methods in the Allow header could be made a bit more specific. Right now it's like this: If the table is insertable, all three methods (POST, PATCH, DELETE) will be output. For one, PUT seems to be missing. And then, what happens for a view that is only updateable, but not insertable?

[wolfgangwalther]

There were two actionable items here:

• Allow option to only generate certain HTTP methods for a DB view #1647 (comment), which is tracked in Add a hint how to prevent auto-updateability of a view postgrest-docs#366
• Allow option to only generate certain HTTP methods for a DB view #1647 (comment), which was fixed in fix: Allow options to generate certain HTTP methods for a DB view #1824