Column Presets as SQL queries/References to Column Values

[browniefed]

It would be great if a column preset could leverage the Hasura claims to then query for another value in the database to insert as the value rather than utilizing the Hasura Claim value only.

[leoalves]

@browniefed,

As a workaround you can create a pre-insert trigger and use the current_setting('hasura.user') in the trigger to get the session details.
#2940
I don't think this is a documented feature but here is an example:

Tables

• user

  • id bigint
  • data text
  • session_variables jsonb
  • user_id_from_session bigint
  • user_name_from_table te xt

• username

  • id bigint
  • name text

Create a Function that will run before the insert getting the session variables and the name from the other table based on the user-id

CREATE OR REPLACE FUNCTION public.user_preset()
  RETURNS TRIGGER 
  LANGUAGE PLPGSQL
  AS
$$
DECLARE
  session_variables json;
  user_name text;
  user_id bigint;
BEGIN
        session_variables := current_setting('hasura.user', 't');
        user_id := session_variables ->> 'x-hasura-user-id';
        SELECT name into user_name FROM public.username where id = user_id;
        NEW.user_id_from_session = user_id;
        NEW.session_variables = session_variables;
	NEW.user_name_from_table = user_name
	RETURN NEW;
END;
$$

Create a trigger to run before the insert and call the function

CREATE TRIGGER set_user_preset
  BEFORE INSERT
  ON public.user
  FOR EACH ROW
  EXECUTE PROCEDURE public.user_preset();

[browniefed]

Oh this is actually super smart, I didn't even think about this.

I'll test it out, should support my use case no problem. Would be totally nice to have it built in.