v1.0.0 Summary Chapter 2: Modeling basics 2. Start modeling 2.5. Custom SQL commands

2.5. Custom SQL commands

In order 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 of 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 in order to speed up productivity preventing the user to type 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 are able to 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.

Feb 23, 2023 at 15:36