PostgreSQL Query
Use this activity to run a simple or a complex SQL Query on a database. The PostgreSQL Query activity returns information in the form of rows.
Settings
The Settings tab has the following field.
Field | Description |
---|---|
Connection | Name of the PostgreSQL database connection from where information is retrieved. You can select the connection from the Connection dropdown list. |
Select Schema |
Select the appropriate schema from the PostgreSQL database. |
Input Settings
The Input Settings tab has the following fields.
Field | Description |
---|---|
Query |
A SQL statement used to query the database. The query can be a simple query or a complex query. A complex query has nested SQL statements. You can construct prepared SQL queries by using substitution variables (or parameters) of the form
As you begin typing a query, the auto-suggest dropdown menu displays relevant keywords, table names, and column names that you can select from. To choose a column name from the dropdown menu, first type the table name followed by a period (.), which shows the list of available column names for that table. If you are unfamiliar with the tables present in the database schema, press the ctrl+ space key in the query editor to obtain auto-suggestion of all the tables and keywords present in the database schema. Each substitution variable identifies an input parameter whose mapped value is substituted into the substitution variable at run time. Input and Output tabs are populated based on the query. End the query with a semicolon (;) to fetch the metadata in the table at the bottom of the query. The following examples represent simple and complex queries: Simple query example:
For the above query, the output fields are generated from the column information of the table
SELECT name, dept_name, tot_cred FROM public.student WHERE dept_name = ?dept_name and tot_cred > ?tot_cred ORDER BY dept_name; For the above query, output fields are generated for
|
Manually Configure Metadata | Set the
Manually Configure Metadata field to
True to fetch table metadata manually. For more information, see
Manually Configuring Metadata.
|
Fields | The grid is provided for informational purposes only. |
Input
This tab contains the input schema. The fields that are selected on the Input Settings tab are available in the schema. You can either hard code their values or map them to a field from the output of a preceding activity in the flow using the Mapper.
Output
This tab displays the activity output schema in a tree structure format. The output of an activity is displayed for informational purposes only and cannot be modified or altered. The information in this schema varies depending on the fields that you selected on the Input Settings tab.
The properties that are displayed on the Output tab schema correspond to the output of this activity and can be used as input by subsequent activities in the flow.
Loop
If you want this activity to iterate multiple times within the flow, enter an expression that evaluates the iteration details. Select a type of iteration from the Type menu. The default type is None, which means the activity does not iterate.
Refer to the "Using the Loop Feature in an Activity" topic in TIBCO Flogo® Enterprise User Guide.
Retry on Error
For more information about the Retry on Error tab, see the "Using the Retry on Error Feature in an Activity" section in the TIBCO Flogo® Extension for Visual Studio Code - Developer User Guide.