hasura
Hasura Session Variables in Native Queries
Is your proposal related to a problem?
I would like to use Session variables in Native Queries. This would allow us to create advanced filters on the SQL level.
Describe the solution you'd like
I would like the same approach as in custom SQL functions:
https://hasura.io/docs/latest/schema/postgres/custom-functions/#accessing-hasura-session-variables-in-custom-functions
Describe alternatives you've considered
I would create an SQL function as an alternative, but I like that approach less.
I'm also interested to have access to hasura-session inside Native Quries.
It seems to be possible to configure session-based permissions on the Logical Models, however it'd be great to also have the capability of using interpolated session variables directly in the Native Query body.
It can help with the following:
- pre-filtering records ahead of a compute-heavy part of the query
- resolving an initial set of records to work with based on the session (and transforming it to the shape of a Logical Model later)
I'm curious if there's anything planned in that regard.
We tried to use an old "hack" with a (Postgres) local session variable: current_setting('hasura.user');, however it doesn't seem to work in the context of a Native Query (perfectly reasonable, granted that it only runs for transactions at the moment)
For sure! Being able to tap into session variables within native queries would be super handy in a bunch of scenarios. With native queries, we can also ensure that the query plan takes advantage of any indexes we've set up based on those session variables.
It is crucial to implement the ability to obtain session data in Native Queries. Currently, performing a simple filter on a table with 300k rows takes 700ms to select 10 rows with a filter and another 700ms to do the "count" on this table. If I run the same queries with the same condition directly on the table, it takes 60ms and 90ms respectively.
