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 in 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. Starting with version 2.0, generic SQL objects also have a Comment field, useful to document the purpose of the stored code.

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 SQL preview tab and seeing how the references were replaced by their respective tracked objects' names.

1

Definition
#	Field	Description
1	SQL code	A valid set of SQL commands to be used as a single database model object. The references of the References tab can be used in the code enclosed by {}, and the special reference {name} stands for the name of the object.

123456

References
#	Field	Description
1	Object	The object of the model the reference points to. Use the buttons beside the field to pick it.
2	Format object name	Indicates that the object's name (or signature, when used) must be automatically double-quoted when special characters are present.
3	Ref. name	The name of the reference to the object. When defined, it must be used enclosed by {} in the tab Definition so the correct object name can be retrieved.
4	Use object signature	Indicates that the signature of the referenced object must be used instead of its name. For objects like functions, casts, operators, and some others, the signature differs from the name because it includes the data types of the parameters and other relevant information.
5	Ref. alias	The alias of the reference to an object. All occurrences of the reference enclosed by @{} are replaced by the referenced object's alias in the SQL code.
6	References	The references defined for the generic SQL object. Each one has a name, an optional alias, and the object it points to.

1

SQL preview
#	Field	Description
1	SQL code	Read-only preview of the SQL code generated for the object with the values currently set in the form.