PostgreSQL Table

Cyclr asks you for the below values when you install the PostgreSQL connector into an account:

Value	Description
Host	The IP address or fully qualified domain name of the PostgreSQL server.
Post	The port to connect to the PostgreSQL server with.
Username	The username to connect to the PostgreSQL server with.
Password	The password to connect to the PostgreSQL server with.
Database	The name of the database on the PostgreSQL server to access.
SSL Connection?	Set to True to connect to the PostgreSQL server using an SSL connection. Set to False to connect to the PostgreSQL server directly. When enabled, the Validate SSL Certificate field needs to be provided.
SSL Certificate	The self-signed SSL certificate to use when establishing an SSL connection to the PostgreSQL server. Set to `False` to disable SSL certificate validation when establishing an SSL connection to the PostgreSQL server.
SSH Tunnel?	Set to True to connect to the PostgreSQL server using an SSH tunnel. Set to False to connect to the PostgreSQL server without an SSH tunnel. When enabling this, SSH Host and SSH Port need to be provided. Which combination of SSH Private Key, SSH Username, and SSH Password is required depends on your SSH host.
SSH Host	The IP address or fully qualified domain of the SSH tunnel to connect to the PostgreSQL server through.
SSH Port	The port of the SSH tunnel to connect to the PostgreSQL server through.
SSH Username	The username to connect to the SSH tunnel with.
SSH Password	The password to connect to the SSH tunnel with.
SSH Private Key	The private key to connect to the SSH tunnel with.

Note: You can use different details for different accounts.

Access a table

Cyclr uses custom object categories to allow the PostgreSQL connector to access multiple database tables with a single connector. To access a table within a database, you need to create a Custom object category.

You can identify custom object categories on the PostgreSQL connector settings page with the icon to the left of the method category.

Add a custom object category

From the PostgreSQL connector settings page:

Under the Methods & Fields heading, select Rows.
Select the Copy this Category to create a Custom object category button.
Select the Select Object dropdown.
Select the schema and table name to access.
Select Copy.

This adds a new method category with the schema and table name you select in step 4. The methods inside the method category target the same schema and table name.

Remove a custom object category

From the PostgreSQL connector settings page:

Under the Methods & Fields heading, select the custom object category you want to remove.
Select the Delete this Custom object category button.
Select Confirm.

Note: You cannot delete custom object categories if you are using methods from within them in cycles.

Select a custom object category primary key

You need to set a primary key when you use a custom object category method that selects a record based on the primary key. Cyclr supports composite primary keys.

The following methods requires you to set a primary key before a request can be sent:

Delete Table Row
Get Table Row
Update Table Row
Upsert Table Row

In the cycle builder, from the Step setup of a Custom object category method:

Select the Ignore dropdown.
Select Lookup.
Select Select....
Select the primary key.

Use functions and procedures

You can use functions when the API returns data. Use procedures if the API doesn't return any data. To manually call functions or procedures, you need the full function or procedure name along with any parameters that you need to enter.

Use these methods to execute PostgreSQL functions and procedures, both built in and user-created, in Cyclr:

For more information, see the PostgreSQL documentation on how to create functions and create procedures.

Find a function or procedure and its parameters

You can find a function or procedure from the PostgreSQL Connector Setup page:

Under the Methods and Fields heading, expand the Functions or Procedures method category.
Select the List Functions or List Procedures method.
Select Test.
Enter any filter parameters you want to add and select Run.

To call the function or procedure, you need the Function Name or Procedure Name. To map custom request fields, you meed the listed Arguments (parameters).

Functions

Call Function

The Call Function method uses a Function Name and optional Parameters to make a request. You can request all functions with the example below:

CODE

SELECT * FROM <Function Name>(<Parameters>)

To call a function:

Find a function and obtain any parameter information.
Add the parameters of the function as custom request fields. Optional, only if parameters are present.
Add the response object fields as custom response fields.
Setup the Call Function method.

Call Function Manually

The Call Function Manually method uses a Function to make a request, and includes the full function name and optional parameters. You can request all manual functions with the example below:

CODE

SELECT * FROM <Function>

To call a function manually:

Find a function and obtain any parameter information.
Add the parameters of the function as custom request fields. Optional, only if parameters are present.
Add the response object fields as custom response fields.
Setup the Call Function Manually method.

Procedures

Call Procedure

The Call Procedure method uses a Procedure Name and optional Parameters to make a request. You can request all procedures with the example below:

CODE

CALL <Procedure Name>(<Parameters>)

To call a procedure:

Find a procedure and obtain any parameter information.
Add the parameters of the procedure as custom request fields. Optional, only if parameters are present.
Setup the Call Procedure method.

Call Procedure Manually

The Call Procedure Manually method uses a Procedure to make a request, this includes the full procedure name and optional parameters. You can request all manual functions with the example below:

CODE

SELECT * FROM <Procedure>

To call a procedure manually:

Find a procedure and obtain any parameter information.
Add the parameters of the procedure as custom request fields. Optional, only if parameters are present.
Setup the Call Procedure Manually method.

Add custom fields

Function or procedure parameters to custom request field

To ensure the function or procedure in the request has the correct format, you can add a function or procedure parameter as a custom request field. You can add the custom request field from the PostgreSQL Connector Setup page:

Under the Methods and Fields heading, expand the Functions or Procedures method category.
Select the Call Function or Call Procedure method.
Under the Request Fields heading, select the + to add a custom request field. Do this for every parameter listed in the previous step:

Field	Description
Field Location	The location of the field in the request. Enter this in the format `parameters.{ParameterName}`. For example, if the parameter is `inputid`, enter `parameters.inputid`.
Display Name	The display name of the field in the Cyclr UI.
Description	The description of the field in the Cyclr UI.
Data Type	The data type of the parameter. Match the data type as closely as possible with the data type of the parameter in PostgreSQL. For example, if the parameter data type is `varchar` then select `Text`.

Select Create.

Response object field as a custom response field

To be able to map a response object field in an integration, you can add a response object field as a custom response field. You can add the custom request field from the PostgreSQL Connector Setup page:

Under the Methods and Fields heading, expand the Functions method category.
Select the Call Function method.
Under the Response Fields heading, select the + to add a custom response field. If you have the response object already, select Generate Fields to generate custom response fields from it. Do this for every field in the response that needs to be mapped in a cycle:

Field	Description
Field Location	The location of the field in the response. Use object keys, separated by `.` for nested objects, as the field location. Use square brackets to indicate arrays in the response object, and use `[].` if the response object itself is an array.
Display Name	The display name of the field in the Cyclr UI.
Description	The description of the field in the Cyclr UI.
Data Type	The data type of the field. Match the data type as closely as possible with the data type of the parameter in PostgreSQL. For example, if the parameter data type is `varchar` then select `Text`.

Select Create.

Setup methods

Call Function or Call Procedure

The Call Function and Call Procedure methods are set up in the same way. You can set them up from the template builder:

Drag the Call Function or Call Procedure method into the cycle builder.
Select Setup step.
Select Select... for the Function Name or Procedure Name field.
Select Lookup.
Select the empty dropdown box under the Selected Value heading.
Navigate the list or use the search feature to find and select the function or procedure to call in the list.
Map any additional functions or procedure parameters that you added as custom request fields.
Select Close.

Call Function Manually or Call Procedure Manually

The Call Function Manually and Call Procedure Manually methods are setup in the same way. You can set them up from the template builder:

Drag the Call Function Manually or Call Procedure Manually method into the cycle builder.
Select Setup step.
Select Select... for the Function or Procedure field.
Select Type a Value.
Enter the function name with parameter brackets. Inlclude any parameters as a comma-separated list inside the parameter brackets, and add the closing semicolon into the field. You need to enclose strings in single quotes, and you need to double up single quotes within strings to escape them. For example, if the function or procedure is named getuserbyid and has an inputid parameter with a value of 6, enter getuserbyid(6). You can use Mergefields from steps you previously mapped here.
Select Close.