v1.1.0 Summary Chapter 3: Database objects 3. Introduction 3.35. Generic SQL objects

3.35. Generic SQL objects

To work around the lack of some database objects, pgModeler implements the generic SQL objects which are nothing more than custom commands wrapped as a database model object. The user can virtually use any valid command as a generic SQL object thus when the model is exported to a script file or to a database server the internal code of all generic SQL objects in the model will be included in the resulting SQL code of the whole database model.

Generic SQL objects are more flexible than custom SQL commands because they are completely independent, and dynamic, and don't need to be attached to any other object in the database model to have the code included in the final result. Additionally, they can store references to other objects on the database model so the SQL code held by the generic SQL object is automatically updated whenever some of the referenced objects change their properties as well. To clarify, in the image below we are creating a foreign table by using a generic SQL object that contains some references to other objects. It's important to note that generic SQL objects are used only in the export process and can't be reverse-engineered moreover they are completely ignored in the database diff operation.

So let's suppose we want to create a foreign table named ftable_test. What we have to do is to use a generic SQL object to store the base definition of the desired object as exemplified by the image above. In the sample code the special attributes {schema}, {name}, and {server} are known as references. Except for {name}, they hold the names of the objects that are used as part of the foreign table's SQL code and they can be configured in the References tab as detailed in the next image. The attribute {name} has another special behavior which is to hold the name of the generic SQL object itself. In this case, it stores the value of the field Name of the object's editing form. This attribute exists as a convenience and allows you to change the name of the generated object just by changing a single field instead of changing the base code definition.

Now, about the references, they are just simple variables that keep track of changes in the names of the referenced objects. In the image above we have defined schema as a reference to the schema public and server as a reference to the foreign server server_test, both objects previously defined in the database model.

Finally, when you have everything configured, you can check on the fly how the code of the generic SQL object (in our example, the foreign table) will look just by focusing on the Preview tab and seeing how the references were replaced by their respective tracked objects' names.

Attribute Description
SQL code A valid set of SQL commands to be used as a single database model object.
Object Object to be referenced.
Ref. name The name of the reference to the object. This one, when defined, must be used surrounded by {} in the SQL code tab so the correct object name can be retrieved.
Use signature Indicates that the referenced object's signature must be used instead of its name. For objects like functions, casts, operators, and some others the signature is slightly different from the name by including parameter data types and some other relevant information.
Format name Indicates that the object's name (or signature when used) must be automatically double-quoted when there's the presence of special characters.
Preview This tab holds a code preview for the configured generic SQL object.

Mar 7, 2024 at 10:40