v1.2.0 Summary Chapter 2: Modeling basics 2. Start modeling 2.5. Custom SQL commands
You're browsing the documentation of a version that is still under development, the features described here may change or even be removed.

2.5. Custom SQL commands

To provide more flexibility when generating SQL code for database objects pgModeler implements a special dialog used to append and prepend arbitrary DML or DDL commands. This is useful, for instance, when you need to populate tables using INSERT commands right after creating them or execute some specific operations before or after creating objects.

Not only INSERT commands are possible to be included but any sort of commands accepted by PostgreSQL, even complete object definitions can be used. This is a great feature but, on the other hand, can cause an unnecessary file bloating in case an excessive amount of code is appended or prepended to objects since any command that is written in the custom SQL dialog is saved into the database model file, so use this feature wisely and with extreme caution.

This dialog offers command templates for SELECT, INSERT, UPDATE, and DELETE to speed up productivity preventing the user from typing the entire commands. When adding commands to the database object there is an additional option that enables the commands to be added at the very end of the database model SQL definition instead of the end of the database definition itself. By the same analogy, you can insert commands at the top of the database model's definition when prepending SQL commands to it.

Finally, the command template buttons include additional options when the user is adding commands to the table's definition. Those buttons can create SQL templates that consider the existent columns of the table. Be in mind that the custom SQL commands are validated only when the SQL validation process is executed. Be careful when using DROP or UPDATE statements since pgModeler will execute them without restrictions.

Despite their usefulness, the custom SQL commands are hardcode instructions that are executed during the exporting process and can't have the execution order changed. For a more dynamic way to handle custom SQL commands, including the execution order or constructing them based upon other objects' names refer to Generic SQL objects on Chapter 3: Database objects.

Oct 31, 2024 at 09:12