Populating the Postgres database functions.
Within PostgreSQL you can create functions (stored procedures) and triggers. These can be used to execute a query when a row is inserted, updated or deleted. Supaboost uses these triggers and functions to update records based on an action.
As stated above, functions are also called stored procedures. This can either be a query or an update statement to a specific table. For instance, one of the functions we use is checking if there is already a user existing for a specific company.
- If there is not, this means that this user is the owner of that project.
- If there is, this means this user got added later
Based on this information, the function is called differently.
The function is executed at a specific time for a specific reason. You can manually execute this stored procedure, but that would not be very automated. That is why triggers are used.
A trigger calls a function if a specified event happens. Events are:
Example: If a user is deleted from the auth.users list, you want to make sure this user is also not visible throughout your application. The deletion is the trigger, which then calls a stored procedure which executes a specific outcome.
Just like we have a table creation statement, for Supaboost we also have a Functions and Triggers creation statement.
Supaboost-V1.0-SQL-Scripts ZIP, open the file
2. Create functions and triggers, then copy the content.
- Log in to your project
- Open the SQL Editor
- Then select
+ New query
New blank query
- Open the Untitled query dropdown
- Enter the name of the text document:
2. Create functions and triggers
- Paste the SQL statement of
2. Create functions and triggersin the Text Editor
- Then select
- If everything went well you should see the result
Success. No rows returned
Now that the Functions and Triggers have been added to the application, we need to make sure that they are actually created.
If you go to Database > Functions, there should be 9 functions. In Database > Triggers, there should be a total of 12 triggers.
At this point you will have a working environment. There is only one small issue — there is no Row Level Security.
This means that any user will be able to see any other user's and other companies information.
Plus they will be able to update this information — let's fix that