Question on postgresql query-level variables

[josh-i386g]

Hi all, first of all thank you for this library, really useful as hell.

This question is on kysely + postgresql, could it be possible to do something like this?

• be able to set a local var before the query?
• be able to unset a local var after the query?

-- Set a local value for a configuration parameter
SET LOCAL my_parameter = 'new_value';

-- Execute some queries with the modified parameter value
SELECT * FROM my_table WHERE some_column = 'some_value';

-- Reset the parameter back to its default or global value
RESET my_parameter;

the use case is we're trying to use row-level security, but postgres must be made aware of the end-user's id which is used on the rls policies.

in postgrest and supabase the equivalent of this is auth.uid() which is used in row-level security policies, but the drawback of that is the entire postgrest stuff being added to the tech stack.

also, it looks like set local x = y; syntax for query-level temporary variables are only supported in postgresql; kinda unsure if it's doable in mysql & sqlite (also unsure about their rls, im mostly a postgres guy and noob in mysql and sqlite). but i think it all comes down to the capability to add statements before and after the query statement.

thank you thank you.

[koskimas]

I don't think you can do that in postgres. There are no variables in postgres outside functions AFAIK. That might work in some client like psql, but that's a client feature, not postgres.

Or are you abusing postgres config variables here? Anyway, that definitely shouldn't be added as a feature to kysely.

[koskimas]

Ok so you did mean config parameters. You need to use raw SQL for this. Since local parameters only live until the end of the transaction, you need to use those inside a transcation:

db.transaction().execute(async (trx) => {
  await sql`SET LOCAL foo = ${value}`.execute(trx)
  // Rest of the transaction here
})