I wish to SELECT id FROM users TABLESAMPLE(10) SYSTEM REPEATABLE ?, ^seed.
I can find no way to do this. Another similar use case would be if I wanted to select from a table function.
This is in Postgres.
You can’t and you can’t. Ecto do not support such queries and all you can do is using raw SQL queries.
1 Like
Hey @gisborne, what @hauleth is getting at is that the query builder provided by Ecto does not provide a way to do this. However, Ecto does have a few ways to let you do raw queries:
Repo.query!("SELECT id FROM users TABLESAMPLE(10) SYSTEM REPEATABLE $1", [seed])
And another: Ecto.Query — Ecto v3.7.1
Ecto can compile so much of what you can do in SQL, this seems a strange omission.
But thanks for the response, all.
Ecto is very explicit that it is not aimed at or built for any specific SQL database, so it tends to not include features or syntax that is only available in a specific one like postgres or mysql.
1 Like
While this is true, the syntax above is SQL standard, so it should be supported by all SQL-standard-conforming implementations.
(Un?)fortunately Ecto chooses to go with “what is common” rather than “what is standard” that is why we have LIMIT (non-standard extension) instead of FETCH FIRST n ROWS (SQL:2008 standard syntax). That mean that we do not have direct access to FETCH FIRST n ROWS WITH TIES, which would allow to fetch more than n rows if more entries ties with the last one according to ORDER BY columns.
Similar thing, I have pointed out earlier that there was no support for NULLS { FIRST | LAST } in Ecto’s :order_by clause, as there is no standardised definition whether NULL is bigger or smaller than any other value wrt. sorting.
3 Likes
Postgres is far from the only database that supports selecting from other than tables and views.
SQL Server also supports table-valued functions, for example.
