how can i use function sum() or max() ,or min() something like that , in my request url.

[mojinfu]

Environment

• PostgreSQL version: (if using docker, specify the image)
• PostgREST version: (if using docker, specify the image)
• Operating system:

Description of issue

(Expected behavior vs actual behavior)

(Steps to reproduce: Include a minimal SQL definition plus how you make the request to PostgREST and the response body)

[mojinfu]

does postgrest support that ?

[steve-chavez]

Check the view example in https://postgrest.org/en/v5.0/api.html#horizontal-filtering-rows (search fresh_stories, bottom of the section).

[mojinfu]

i exactly known what you mean, and exactly known how to do ..
but I need a api which will output summed
data by a time range I give.
so if I creat a view with summed data , time column will not in this view.. I can not make up a api with a time range parameter.

[mojinfu]

and i found a way to solve the problem by create a function to sum data . and the time range parameter as the function parameter.
but this way is so getting complex when i sum group by something else, it not support page split natively.

[mojinfu]

i got a theory : if PostgREST do not support data calculation (like sum, min..) functions in request url, then PostgREST is not able to do any data calculation job without creating functions in sql, it is only able to show data.
@steve-chavez

[steve-chavez]

Stored procedure is another option, post a sample of the query you want to achieve to see how it can be done.

Edit: posted comment without refreshing the page, nvm.

[steve-chavez]

To create more complex queries than the ones PostgREST supports out of the box you have to use stored functions, this is explicitly stated in the docs in https://postgrest.org/en/v5.0/api.html#custom-queries.

It may be possible in the future to support aggregate functions, see #915 for a discussion on this, but for now this is not possible as we require support for restricting the statement_cost in PostgreSQL.

[mojinfu]

When I create a function as a api .
how can I set a count number into response header like the formate that (0-20/132)??

[mojinfu]

i found it already has been added into my header . great !!!!

[steve-chavez]

Good, considering the issue resolved then.

[rrdlpl]

@mojinfu I also need this in my project. Could you please tell me how you solved this issue?

[mojinfu]

@mojinfu I also need this in my project. Could you please tell me how you solved this issue?

add a special header into http request ,"sorry i cant remember that header name ."

and you can get count num in response header.

[mojinfu]

Good, considering the issue resolved then.

but i used prest to do the same thing. prest is more clear and better . i thought.

see this https://github.com/prest/prest
you need do a little modify to prest if you want to get the count number in prest
(i have done this , easy job.)

[steve-chavez]

@rrdlpl You could create a view like this:

create view employees_summaries as 
select 
  min(salary), 
  max(salary), 
  avg(salary) 
from employees;

And then request it with GET /employees_summaries.

If the queries are more complicated you could do a similar thing with stored procedures.

[fstn]

But how we can have this view and be able to filter it for example by date? I don't want to implement all the postgrest features inside my procedure don't want to par all operator to reconstruct the query