PostgreSQL Actions¶
Actions are the operations (or API operations) that you can perform on PostgreSQL database application.
You can simply drag and drop elements and values from input trigger, derived values using formulas or desired constant that you may wish to map with PostgreSQL .
You can automate the PostgreSQL query executions which you do manually otherwise.
As shown below in Figure 1, you can Create, Update, Delete, and Upsert records in database tables based on response of the trigger event.
Create Record¶
Create records in the selected database table when an event triggers.
- Select the table from the database in which you have to create a record. After the table selection, all the
Not Null
columns of your tables will be shown as required fields. - Add optional fields of the table using the
+
button. You can map field data using the data coming from the trigger or use excel functions. You can also use the evaluate functionality to map values for enum type columns.
For example, as shown below Figure 2, values of incident
table multiple columns are mapped to attributes from ServiceNow.
Delete Record¶
Delete one or multiple PostrgreSQL database records in response to the event trigger. You can filter records based on the selection criteria.
- Select the table in your database from which you want to delete the records.
- Use the
Choose where condition
to add the conditions for selecting the records to be deleted. You can construct complex logic of AND and OR conditions to select the records.
For example, in Figure 3 below, from the table incident
, all the records which have incident_name same as category
of ServiceNow incident and incident_description
equal to Short Description
will be deleted when an incident updated in ServiceNow (Privileged).
Update Record¶
Update one or multiple PostgreSQL database records in response to an event trigger. You can update the record from the table based on some condition.
- Select the table in your database which you want to update.
- Add the conditions to select the records that will be updated. You can construct the logic of conditions using complex conditional OR and AND.
- Select the updates that you want to perform on the selected records. You can use data Mapping and Transformation and excel functions. You can also use the evaluate functionality to map values for enum type columns.
In the figure below, whenever Incident updated on ServiceNow (Privileged), a record is updated in PostgreSQL database. The database table incident_description
and incident_name
columns are mapped to attributes from ServiceNow.
In the time_of_incident
column, the current date and time can be added using the excel function Now()
.
Upsert Record¶
Upsert operation is the combination of Update and Create(insert) operations. Connect iPaaS will find if a record with matching Primary Key is present in the table. If the record found, the record will be updated with the data provided. If the record is not found, Connect iPaaS will create a new record with the data provided. The Primary key can either be a single column or a combination of columns.
In the figure below, whenever an Incident is updated on ServiceNow (Privileged), a record is updated or added in PostgreSQL database. The database table incident_name
and incident_description
column are mapped to attributes from ServiceNow. The time_of_incident
column is populated with the current time using the excel style function Now()
.
If your database table has a column with ENUM data type, you can map the value of this field using the evaluate functionality by enabling the Evaluate
checkbox.