Comments (8)
as postgres supports [...], please add similar functionality to PostgREST
PostgreSQL supports a lot of stuff, that PostgREST doesn't. Adding full support for everything that PostgreSQL does is certainly not achievable. I wouldn't consider this a valid argument for supporting anything.
[...]
order by random()
[...]
We can look at this from two different angles:
- We have not implemented calling arbitrary functions in filters or order parameters. If we implemented that, calling random should work, too. However, I don't see us implementing generic function support for a variety of reasons, one of them the inability to use indexes and the problem of DoS attacks.
random()
feels like kind of a special case, because it does not depend on any other column - so there is not really a point of using an index at all. Not sure how many other expressions that don't depend on any columns make sense to use, butrandom()
certainly does.
In any case, I think you can already use it right now with a virtual / computed column. Just define it like this:
create table my_table (...);
create function random(my_table) returns double precision
language sql as 'select random()';
You can then use it as a column on this endpoint:
GET /my_table?order=random
You could probably generalize the function to take any
as the argument type, to allow this for all your endpoints.
from postgrest-docs.
order by random()
will always cause a full table scan, so I don't think we'll support it.
Supporting TABLESAMPLE sounds more likely to happen.
More details at: https://www.2ndquadrant.com/en/blog/tablesample-and-other-methods-for-getting-random-tuples/
from postgrest-docs.
TABLESAMPLE seems a bit complex to expose to clients.
Since doing
create function random(anyelement) returns double precision
language sql as 'select random()';
Is pretty simple and makes random available for all tables, we could turn this into a how-to
in docs.
This depends on PostgREST/postgrest#2442, because we'd need to be able to restrict to which columns the order by can be applied.
from postgrest-docs.
Similarly to the estimated count, perhaps we can apply the order by random()
for low counts and then TABLESAMPLE for higher counts.
We could do it through an Accept: application/vnd.pgrst.random+json
.
Edit: Maybe a header isn't right here as different media types can support random.
from postgrest-docs.
pg16 has a new any_value
aggregate: https://www.postgresql.org/docs/16/functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE
Add aggregate function ANY_VALUE() which returns any value from a set
Haven't tried it yet but seems it could be used in conjunction with custom media types to get a random row for any relation.
from postgrest-docs.
Add aggregate function ANY_VALUE() which returns any value from a set
Haven't tried it yet but seems it could be used in conjunction with custom media types to get a random row for any relation.
It does not return a random row, however. It returns "a" row. IIRC, the current implementation is that it returns the "first" rows it finds. That's certainly not going to be a true random.
from postgrest-docs.
IIRC, the current implementation is that it returns the "first" rows it finds. That's certainly not going to be a true random.
Ah, ok. Then it's like first
on: https://wiki.postgresql.org/wiki/First/last_(aggregate)
from postgrest-docs.
Ah, ok. Then it's like first on
Nope, it's not, because this is only an implementation detail and not guaranteed to stay that way. You can't rely on it.
from postgrest-docs.
Related Issues (20)
- How-to for dynamic schemas with `pre-config` HOT 1
- serve image with img tag HOT 1
- Readthedocs will stop working on September 25 with the current config file HOT 1
- Docker crashing on M1 HOT 3
- limiting HTTP verbs in openapi response HOT 3
- Link to Installation from tutorial
- Move binary installation from tut0.rst to install.rst and add install options to tabs
- Library not loaded on Mac HOT 4
- Rename admin page name to Observability
- Deprecated "External JWT Generation" section using Auth0 Rules
- Chocolatey doesn't add `postgrest` to the PATH
- Drop all plain HTTP snippets in favor of `curl` commands HOT 1
- Missing entries in Preferences section HOT 1
- Show a more prominent version number
- Having more than one internal schema on schema isolation is confusing HOT 4
- Use the term "secret" instead of "password" in Tutorial 1
- Avoid Globbing in Curl examples HOT 2
- Move from tailwind to PicoCSS in HTMX how-to
- Expand on Schema Isolation HOT 2
- Recommend using `row_security = off` for starting up with RLS HOT 3
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from postgrest-docs.