PostgreSQL Insert
Use this activity to run an SQL Insert to insert the records into the database and return the information based on the returning clause specified in the insert query.
Settings
The Settings tab has the following fields:
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 |
---|---|
Insert |
An SQL statement used to insert a record in the table. 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. If the query contains parameters then also parameter metadata is populated. End the query with a semicolon (;) to fetch the metadata in the table at the bottom of the query. Substitution variables used in the Values clause that match column names in the table are displayed under the Values array, while those that do not match are displayed under Parameters on the Input tab. Use Values to insert multiple records at once and use Parameters to insert only one record at a time. Map JSON data from the previous activity for the insert when you use:
The following examples represent insert queries: INSERT INTO public.product (product_no, name, price) VALUES (1, 'Cheese', ?price), (2, 'Juice', ?price), (3, 'Milk', ?price ) returning (select name from public.instructor (where name = ?name);For the above insert query, the output field is generated for the name and the
input field is generated for the
price (NUMERIC) under the Values[] node as its part of the values clause, and the name(VARCHAR) under the parameters node as it is part of the parameter select sub-query. Also, the mapped value for the field price and name is substituted into the substitution variable
?price and
?name .
INSERT INTO public.product (product_no, name, price) VALUES (?product_no, ?name, ?price) returning price;For the above insert query, the output field is generated for the price , and
input fields are generated for
product_no (INTEGER),
name (TEXT), and
price (NUMERIC). Also, the mapped value for the field
product_no ,
name , and
price is substituted into the substitution variables
?product_no ,
?name ,
?price . The parameters node on the
Input tab has mappings as there are no parameters in the insert query statement.
|
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 displays the input schema of the activity as a tree structure. The information in the schema varies based on the insert query provided. The fields that are selected on the Input Settings tab are available in the schema. You can either hard code the values or map them to a field from the output of a preceding activity in the flow using the Mapper.
Fields from the RETURNING clause are displayed under the Parameters node and fields from the VALUES clause are displayed under the VALUES node in the input schema.
Output
This tab displays the output schema of the activity as a tree structure. The output is read-only. The information in the schema varies based on the fields selected on the Input Settings tab. The properties that are displayed in the 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.