Using group by in postgREST

Question

I'm new to Postgres and postgREST. I would need to receive distinct records from one column - ideally with possibility to filter.

Task I need to solve is in SQL

select distinct "column1" where "column2" = "value2 and "column3" = "value3"

column1, column2, colum3 and values should be parameters of the REST call.

in postgREST I will likely need to use a function or stored procedure, but I'm new to this.

Can you please help?

score 2 · Answer 1 · answered Sep 27 '19 at 19:08

2

I'd go with creating three VIEWs:

CREATE VIEW table_dist_column1 AS
  SELECT
    DISTINCT ON(column1)
    column1
  , column2
  , column3
  FROM table;

CREATE VIEW table_dist_column2 AS
  SELECT
    DISTINCT ON(column2)
    column1
  , column2
  , column3
  FROM table;

-- Analogously:
-- CREATE VIEW table_dist_column3 AS ....

Then it would be possible to use PostgREST filters normally(e.g. curl "<url>/table_dist_column1?column1=eq.baz&column2=eq.foo&column3=eq.bar").

It's also possible to do it with a single plpgsql stored procedure with dynamic sql(using quote_ident). But I wouldn't recommend this since it could be dangerous to expose to end users.

So the VIEWs are the best option.

answered Sep 27 '19 at 19:08

Steve Chavez

931
10
13

Thanks. That works in some limited cases, but I was thinking to dynamically create tables (for different inputs) and then do selection queries for that. This would mean that for each table I would have to create custom view. My thinking is more around having "a proxy" which user will call and this will then generate postgrest query with some restrcitions. As giving ability to the use to get all the data from the table (if there is 1 mil row) is also not a great practice. – Bill Sep 29 '19 at 08:25
For your 1 mil row concern(though that's orthogonal to the question) PostgREST provides [max-rows](http://postgrest.org/en/v6.0/configuration.html#max-rows) which puts a hard LIMIT for all queries. Also, PostgREST is already "a proxy"(or a sandbox) and it only exposes safe(fast) queries. Now, If you're going to dynamically create the tables(do you mean dynamic SQL?), why not dynamically create the VIEWs too? – Steve Chavez Sep 30 '19 at 01:17
@Bill I've added another answer that is simpler. Mind the `format` function though. – Steve Chavez Nov 13 '19 at 00:40

score 1 · Answer 2 · answered Nov 13 '19 at 00:39

Another option would be using dynamic sql in a stored procedure and being very careful about it by using the pg format function. Suppose we have a projects table, then we create this function:

create function projects(dist name) returns setof projects as $$
begin
  return query execute format('select distinct on(%I) * from projects', dist);
end; $$ language plpgsql;

Now you can use all of PostgREST filters on this function result:

## Filtering by a column
curl "localhost:3000/rpc/projects?id=eq.1&dist=name"

## Selecting only certain columns
curl "localhost:3000/rpc/projects?select=id,name&dist=name"

Using group by in postgREST

2 Answers2