PostgreSQL Insert

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 drop-down list.

Input Settings

The Input Settings tab has the following fields:

Field Description

Insert

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. 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 only parameter metadata is populated. End the query with a semicolon (;) to fetch the metadata in the table at the bottom of the query. When substitution variables used in the VALUES clause are identical to the column names in the table, those variables are displayed under the Values array and otherwise 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: Values - https://github.com/TIBCOSoftware/tci-flogo/tree/master/samples/app-dev/Mapping-Arrays/array.forEach.sample Parameters - https://github.com/TIBCOSoftware/tci-flogo/tree/master/samples/app-dev/Flow-Design-Concepts/loops.sample The following examples represent insert queries: INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', ?price), (2, 'Juice', ?price), (3, 'Milk', ?price ) returning (select name from instructor where name = ?name); For the above insert query, the output field is generated for `name` and `input` field is generated for the `price` (NUMERIC) under Values[] node as its part of values clause, and name(VARCHAR) under 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 products (product_no, name, price) VALUES (?product_no, ?name, ?price) returning price; For the above insert query, 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`, `price` is substituted into the substitution variables `?product_no`, `?name`, `?price`. The parameters node on the Input tab have mappings as there is no parameter 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 Configure Metadata.
Fields	The grid is provided for informational purposes only.

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.

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 only parameter metadata is populated.

End the query with a semicolon (;) to fetch the metadata in the table at the bottom of the query. When substitution variables used in the VALUES clause are identical to the column names in the table, those variables are displayed under the Values array and otherwise 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 products (product_no, name, price) VALUES (1, 'Cheese', ?price), (2, 'Juice', ?price), (3, 'Milk', ?price ) 
returning (select name from instructor where name = ?name);

For the above insert query, the output field is generated for name and input field is generated for the price (NUMERIC) under Values[] node as its part of values clause, and name(VARCHAR) under 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 products (product_no, name, price) VALUES (?product_no, ?name, ?price) returning price;

For the above insert query, 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, price is substituted into the substitution variables ?product_no, ?name, ?price. The parameters node on the Input tab have mappings as there is no parameter 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 Configure 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 RETURNING clause are displayed under Parameters node and fields from VALUES clause are displayed under VALUES node in 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 corresponding to the output of this activity and can be used as input by subsequent activities in the flow.

Loop