The function trigger can be re-used for many tables, but we need to tell Postgres what table to apply it to. Now that we have our function all setup we need to create our trigger. Now all that is left to do is run in the Data > SQL tab and save it as a migration.
Then finish of by returning NEW for Postgres to save it. So here we can now set the field we want which is createdBy to our staff_id.
#Postgresql insert trigger update#
For UPDATE there would also have OLD which would be the same row but old values. For inserts you have access to NEW which is the new row that will be inserted. staff where "auth0_user_id" = auth_zero_user_id ĭuring a function trigger there are helper variables that Postgres offers. The key is then selecting the id to be placed into our variable staff_id. We search out staff table, where the column auth0_user_id is equal to our auth_zero_user_id variable from our session variables. For more Postgres JSON operations you can see those here session_variables : = current_setting( 'er', 't' ) auth_zero_user_id : = session_variables -> 'x-hasura-user-id' We again use := assignment operator and then use -> (get JSON object field as text) to instruct postgres to get the x-hasura-user-id and pass that back to our variable auth_zero_user_id. Next we need to pull out the x-hasura-user-id from our session_variables. We are assigning the hasura user into session_variables which is json.
The current_setting('er', 't') call is only available during mutations, and is not available during queries. Meaning if the er setting isn't set during the query it's okay, and we will progress anyway. The er is set by hasura, and the t specifies that missing_ok. The first line uses the current_setting() function. staff where "auth0_user_id" = auth_zero_user_id NEW. BEGIN session_variables : = current_setting( 'er', 't' ) auth_zero_user_id : = session_variables -> 'x-hasura-user-id' SELECT id into staff_id FROM public. DECLARE session_variables json staff_id text auth_zero_user_id text We do need to declare the types, so in our case session_variables is json and the other 2 are just text. Finally we will then query for our actual id we want to insert and store that in a separate variable. Then we will pull out our hasura id from the variables and store it in another variable. Next we declare what variables we are going to use. So overall it's a nice method to define start/end and make it easier when dealing with strings. Then anytime you needed to use single quotes they would have to be escaped. Otherwise the whole function would be have to wrapped in single quotes. The dollar-quoted string constraints are a way to inform Postgres that the following is all text. Not only does the $$ signs signify beginning and end of the function it has a meaning. staff_created_by_preset() RETURNS TRIGGER LANGUAGE PLPGSQL AS
Postgres has additional languages you can use if installed, like python. Specifically the name, what we are creating and the language. The start tells Postgres a little bit of information. staff_created_by_preset() RETURNS TRIGGER LANGUAGE PLPGSQL AS $$ DECLARE session_variables json staff_id text auth_zero_user_id text BEGIN session_variables : = current_setting( 'er', 't' ) auth_zero_user_id : = session_variables -> 'x-hasura-user-id' SELECT id into staff_id FROM public. There is a lot of boilerplate and it can look intimidating but we'll break things down piece by piece so you can understand. I don't want to track people based upon their Auth0 user id, and additionally don't want to require a person to pass back a user_id for doing inserts. These users also exist in our database with a UUID. The specific problem space for me is that one set of our uses logs in with Google via Auth0. As a solution we will leverage Postgres functions. Hasura does not yet offer a way to reference the database for the column presets to retrieve a value. There might be a time when the value you need to insert is not actually in the JWT. This means if a user creates an item we can have a created_by value inserted that is their user id.Ĭombined with select permissions this allows for the user to always create stuff that belongs to them and additionally access it. These allow you to add static values, or values from the Hasura claims in the JWT upon insert/update. Hasura offers a concept called Column Presets.
0 kommentar(er)
