PostgreSQL Insert
Use this activity to execute an SQL Insert to insert the records into the database and return the information based on the returning clause specified in the insert query.
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.
Each substitution variable identifies an input parameter whose mapped value is substituted into the substitution variable at runtime. You can reuse the substitution variable for the same input parameter elsewhere in the insert query. The type information for the input parameters used in the VALUES and RETURNING clause is fetched from the database using the selected connection for the entered insert query. Also based on the output parameters used in the RETURNING clause, the corresponding type information is fetched from the database using the selected connection for the entered insert query. Similarly, input and output fields on the
Input and
Output tabs of the activity are also populated based on the SQL Insert statement.
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, output field is generated for name and input field is generated for 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 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 will not have mappings as there is no parameter in the insert query statement. |
| Manually Configure Metadata | If you see an error for a valid SQL statement in the Query field, set the Manually Configure Metadata field to True to fetch table metadata manually using simple SELECT statement. 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 were selected on the Input Settings tab will be 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 correspond to the output of this activity and can be used as input by subsequent activities in the flow.