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 ?<fieldname> in the query statement.

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:

For more details about Data Mappings, see "Mapping JSON Data with the json.path() Function" section in the TIBCO Flogo Enterprise User Guide

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.

  • Call Procedure and Call Functions Syntax:
    • With no parameters
      Call  Procedure_Name( );
      
      Select Function_Name( );
      
    • With prepared parameters
      Call  Procedure_Name(?Param1 , ?Param2);
      
      Select Function_Name(?Param1, ?Param2);
      
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.