Creating a trigger function in pgModeler
In a certain kingdom, in a certain state ... it took me to add a trigger to the model on pgModeler . What to do is easy enough. But to add a trigger function ... It is also easy, but I had to sort out a bit with the parameters offered for filling / selection in the interface.
pgModeler is a very good database design tool that can generate sql scripts for PostgreSQL . Details about this tool and its capabilities can be found on the official site .
As an example, consider a simplified model with a single table.
')
Add a function to the model.
After that, a window will open with various editable parameters with which the function will be created. In this case, some fields will already be filled with default values.
Consider these options in more detail.
I think with the parameters Name , Schema , Owner and Comment everything is clear - this is the name of the function, database schema, owner and comment for this function, respectively.
Language is the name of the language in which the function will be implemented. To be honest, I have never had to write functions for PostgreSQL on anything other than plpgsql . Therefore, it is this value that was chosen for the parameter.
Return Method . Since in the trigger function neither the table ( Table ) nor the set ( Set ) of values is needed, we leave Simple .
With the parameters in the Data Type block, in general, everything is also simple. Since the function will be called in the trigger, then in the Type field we specify the trigger (the Format field will be automatically filled with the value of the trigger ). The Dimension field (the only undetected digital field in this block) is needed to indicate the dimension of the return value array. But since we just need one value, not an array, we leave 0 in this field.
With the remaining parameters, everything is not so obvious, at least for me, because I never had to think about them in the normal creation of a function in PostgreSQL .
Function Type can take one of three values: IMMUTABLE , STABLE and VOLATILE . From the official PostgreSQL documentation, you can find out that these arguments inform the query optimizer about the behavior of the function.
Accordingly, if the trigger function requires changing the database, then IMMUTABLE is not appropriate. The function with the STABLE parameter is not suitable for AFTER triggers that want to read lines modified by the current command. It remains VOLATILE , which is missing the above problems. It will also be specified by default if none of the above arguments are specified when creating the function.
Security can take one of two values: SECURITY DEFINER and SECURITY INVOKER and is responsible for which user it will be called with.
The default is SECURITY INVOKER , so you can leave it.
Behavior can take one of three values: STRICT , RETURNS NULL ON NULL INPUT and CALLED ON NULL INPUT and shows how the function will behave if its arguments are NULL values.
The default is CALLED ON NULL INPUT . Therefore, you can also leave it.
Rows Returned shows the number of rows that the scheduler will expect. The value is specified for functions that return sets. Since our function returns one value, leaving 0 .
Execution Cost sets the cost of executing this function for the scheduler. For the plpgsql language, it defaults to 100 . Therefore, this value and indicate.
Windown Func. means that a window function will be created. In our case, because we need a trigger function, we don’t need to specify this value (well, in general, it’s written in the documentation itself that it makes sense to specify this parameter only for functions written in C).
Leakproof indicates that the function is sealed, i.e. that it does not disclose information about its arguments (for example, it does not display their values in the error message), except for returning the result. Since the trigger function takes no arguments, this parameter does not need to be specified.
So, with the parameters of the function is over. The function body itself can be written in the same window on the Definition tab. We proceed to the creation of the trigger itself.
After that, the create trigger window will appear.
Consider the parameters that can be set in this window.
With the parameters Name , Alias and Comment again everything is clear - this is the name of the trigger, alias and comment to the trigger, respectively.
Excution shows how this trigger will be executed and can take one of the following values: BEFORE , AFTER and INSTEAD OF , - which means that the function will be executed before, after or instead of the event.
FOR EACH ROW determines whether the trigger procedure will fire once for each row. If not specified, the FOR EACH STATEMENT parameter will be set, which determines that the trigger procedure is triggered once for the SQL statement.
An event determines which events need to be processed in a given trigger. You can specify multiple events. Events are of the following types: INSERT , UPDATE , DELETE and TRUNCATE . They occur when the corresponding command with the same SQL statement is called.
Constraint indicates that a limit trigger will be created. Constraint triggers are used to generate exceptions when a constraint is violated. You can read more about them in the official documentation .
For a constraint trigger, you can specify Deferrable , which defines the trigger time. This parameter can be one of the following values: INITIALLY IMMEDIATE or INITIALLY DEFERRED .
Refer. Table - the name of the table to which the constraint refers. It is used for foreign key constraints and is allowed only for constraint triggers.
Condition is a condition that determines whether a trigger function will be executed. For FOR EACH ROW triggers in this field, you can access the old and new values through OLD and NEW, respectively (that is, the same as in the body of the trigger function).
Arguments - the list of arguments that will be passed to the trigger function when the trigger fires. String constants are passed as function arguments.
Columns - can be specified only for UPDATE events. The trigger will work only when there is at least one of the specified columns in the list of columns specified in UPDATE .
Here, in general, that's all. I hope it was interesting and will be useful to someone.
When writing the article, pgModeler version 0.9.2-alpha was used, compiled under Windows 7 x64. When using older / newer versions of pgModeler, slight differences in the interface are possible.
The model used in the article can be downloaded here .
pgModeler is a very good database design tool that can generate sql scripts for PostgreSQL . Details about this tool and its capabilities can be found on the official site .
As an example, consider a simplified model with a single table.
')
Add a function to the model.
After that, a window will open with various editable parameters with which the function will be created. In this case, some fields will already be filled with default values.
Consider these options in more detail.
I think with the parameters Name , Schema , Owner and Comment everything is clear - this is the name of the function, database schema, owner and comment for this function, respectively.
Language is the name of the language in which the function will be implemented. To be honest, I have never had to write functions for PostgreSQL on anything other than plpgsql . Therefore, it is this value that was chosen for the parameter.
Return Method . Since in the trigger function neither the table ( Table ) nor the set ( Set ) of values is needed, we leave Simple .
With the parameters in the Data Type block, in general, everything is also simple. Since the function will be called in the trigger, then in the Type field we specify the trigger (the Format field will be automatically filled with the value of the trigger ). The Dimension field (the only undetected digital field in this block) is needed to indicate the dimension of the return value array. But since we just need one value, not an array, we leave 0 in this field.
With the remaining parameters, everything is not so obvious, at least for me, because I never had to think about them in the normal creation of a function in PostgreSQL .
Function Type can take one of three values: IMMUTABLE , STABLE and VOLATILE . From the official PostgreSQL documentation, you can find out that these arguments inform the query optimizer about the behavior of the function.
- IMMUTABLE means that the function cannot modify the database and always returns the same result with certain values of the arguments.
- STABLE means that the function cannot modify the database, and within the same table scan it always returns the same result for certain values of the arguments.
- VOLATILE means that the result of a function can change even within a single table scan, so its calls cannot be optimized.
Accordingly, if the trigger function requires changing the database, then IMMUTABLE is not appropriate. The function with the STABLE parameter is not suitable for AFTER triggers that want to read lines modified by the current command. It remains VOLATILE , which is missing the above problems. It will also be specified by default if none of the above arguments are specified when creating the function.
Security can take one of two values: SECURITY DEFINER and SECURITY INVOKER and is responsible for which user it will be called with.
- SECURITY DEFINER means that the function will be executed with the rights of the user who owns it, i.e. the one that was listed in the Owner .
- SECURITY INVOKER means that the function will be executed with the rights of the user who called it.
The default is SECURITY INVOKER , so you can leave it.
Behavior can take one of three values: STRICT , RETURNS NULL ON NULL INPUT and CALLED ON NULL INPUT and shows how the function will behave if its arguments are NULL values.
- RETURNS NULL ON NULL INPUT or STRICT means that the function will always return NULL if at least one of its arguments is NULL.
- CALLED ON NULL INPUT means that the function will be called as usual, even if its arguments are NULL.
The default is CALLED ON NULL INPUT . Therefore, you can also leave it.
Rows Returned shows the number of rows that the scheduler will expect. The value is specified for functions that return sets. Since our function returns one value, leaving 0 .
Execution Cost sets the cost of executing this function for the scheduler. For the plpgsql language, it defaults to 100 . Therefore, this value and indicate.
Windown Func. means that a window function will be created. In our case, because we need a trigger function, we don’t need to specify this value (well, in general, it’s written in the documentation itself that it makes sense to specify this parameter only for functions written in C).
Leakproof indicates that the function is sealed, i.e. that it does not disclose information about its arguments (for example, it does not display their values in the error message), except for returning the result. Since the trigger function takes no arguments, this parameter does not need to be specified.
So, with the parameters of the function is over. The function body itself can be written in the same window on the Definition tab. We proceed to the creation of the trigger itself.
After that, the create trigger window will appear.
Consider the parameters that can be set in this window.
With the parameters Name , Alias and Comment again everything is clear - this is the name of the trigger, alias and comment to the trigger, respectively.
Excution shows how this trigger will be executed and can take one of the following values: BEFORE , AFTER and INSTEAD OF , - which means that the function will be executed before, after or instead of the event.
FOR EACH ROW determines whether the trigger procedure will fire once for each row. If not specified, the FOR EACH STATEMENT parameter will be set, which determines that the trigger procedure is triggered once for the SQL statement.
An event determines which events need to be processed in a given trigger. You can specify multiple events. Events are of the following types: INSERT , UPDATE , DELETE and TRUNCATE . They occur when the corresponding command with the same SQL statement is called.
Constraint indicates that a limit trigger will be created. Constraint triggers are used to generate exceptions when a constraint is violated. You can read more about them in the official documentation .
For a constraint trigger, you can specify Deferrable , which defines the trigger time. This parameter can be one of the following values: INITIALLY IMMEDIATE or INITIALLY DEFERRED .
- INITIALLY IMMEDIATE means that the trigger will be triggered after each statement.
- INITIALLY DEFERRED means that the trigger will only fire at the end of the transaction.
Refer. Table - the name of the table to which the constraint refers. It is used for foreign key constraints and is allowed only for constraint triggers.
Condition is a condition that determines whether a trigger function will be executed. For FOR EACH ROW triggers in this field, you can access the old and new values through OLD and NEW, respectively (that is, the same as in the body of the trigger function).
Arguments - the list of arguments that will be passed to the trigger function when the trigger fires. String constants are passed as function arguments.
Columns - can be specified only for UPDATE events. The trigger will work only when there is at least one of the specified columns in the list of columns specified in UPDATE .
Conclusion
Here, in general, that's all. I hope it was interesting and will be useful to someone.
When writing the article, pgModeler version 0.9.2-alpha was used, compiled under Windows 7 x64. When using older / newer versions of pgModeler, slight differences in the interface are possible.
The model used in the article can be downloaded here .
Source: https://habr.com/ru/post/424121/
All Articles