Skip to content
background-image background-image

Postgre SQL Database connector

[ | version 3.3]

Building a comprehensive content for you: A Work in Progress...

Connector

The connector is permitted for use of the platform background agent.

Connector processing type: Both (Row by row & Bulk), Default type: Bulk!

The Postgre SQL Database connector allows you to execute SQL commands and stored procedures remotely in Postgres database.

Accessing input data

If the input schema is selected, the input data will be predefined in the temporary table InputData (see Predefined Variables). You can use entire table in your statements or target concrete value by using placeholder ${input}:

Placeholder Type Description
${inputData} temporary table Temporary table filled with input data. This table will be deleted after the connector run is over.
${input[<row number>].<column name>} value Takes the value from <row number>th row (zero-based index) and column named <column name>.

Connector is able to process complex data columns on input schema. Such data are represented as text column and filled with serialized JSON.

Using placeholders

Assume the connector input data in the following format, followed by placeholders, which can be used for direct reference to the input data table:

Row number ID Login
0 1 ${input[0].ID} Alice ${input[0].Login}
1 2 ${input[1].ID} Bob ${input[1].Login}

Returning all input data

select * from InputData;

Returns the contents of the entire temporary table filled with input data:

ID Login
1 Alice
2 Bob

Referring to a specific value from the input data

Selected columns must be always wrapped with double quotes and must respect case sensitive names.

select 
    ${input[0].ID} as ID, 
    "Login" as Login
from InputData

Returns the list as in the previous example, except that the value from the first row and column of the ID input table is always used as ID (${input[0].ID} = 1):

ID Login
1 Alice
1 Bob

Predefined variables

Variable Data type Description
InputData temporary table Temporary table filled with input data. This table will be deleted when the connector is finished running.
@TaskRunID text ID of the currently executing TaskRun. Data type should be fixed in future.
@EndpointTokenID integer ID of the currently used EndpointToken.
@EndpointTokenName text Name of the currently used EndpointToken.
@DataCheckpoint text Datacheckpoint value.

Returning output data

If the output scheme is selected, the result of the last select statement, which is not assigned to any variable, is used as the step output. The structure of the query result must match the structure of the output schema.

Selected columns must be always wrapped with double quotes and must respect case sensitive names.

If there are some complex input schemas returned to output, the result will contain column data serialized as JSON string.

Example

-- NOT the last select in the statement, data will NOT be sent to the output.
select 0 as "ID", "Login" from InputData;

-- IS the last valid select in the statement, data WILL be sent to the output.
select 1 as "ID", "Login" from InputData;

-- Using variable as parameter
select "ID" as TaskRunID, @DataCheckpoint as DataCheckpoint from InputData where "ID" != @TaskRunID
select @TaskRunID as TaskRunID

Logging

Direct logging from SQL connector into task run log is not supported.

Configuration

Postgre Connection string

Connection string

Input data can be obtained with input data placeholders.

${input.DbName} will be replaced by value in DbName of the very first row.

Standard

Host=myserver;Username=mylogin;Password=mypass;Database=mydatabase

User ID=root;Password=myPassword;Host=localhost;Port=5432;Database=myDataBase;Pooling=true;Min Pool Size=0;Max Pool Size=100;Connection Lifetime=0;

Using windows security
Server=127.0.0.1;Port=5432;Database=myDataBase;Integrated Security=true;
Setting command timeout
Server=127.0.0.1;Port=5432;Database=myDataBase;User Id=myUsername;Password=myPassword;CommandTimeout=20;
Setting connection timeout
Server=127.0.0.1;Port=5432;Database=myDataBase;User Id=myUsername;Password=myPassword;Timeout=15;

For more examples of connection strings, see official Npgsql documentation or unofficial documentation.

Postgre Statement

Statement

Postgre statement to be executed. If the input schema is selected, the input data will be predefined in the temporary table InputData. You can use entire table in your statements or target concrete value by using following placeholders:

Placeholder Type Description
${inputData} temporary table Temporary table filled with input data. This table will be deleted after the connector run is over.
${input[<row number>].<column name>} value Takes the value from <row number>th row (zero-based index) and column named <column name>.
@TaskRunID integer ID of the currently executing TaskRun.
@EndpointTokenID integer ID of the currently used EndpointToken.
@EndpointTokenName text Name of the currently used EndpointToken.
@DataCheckpoint text Datacheckpoint value.

If the output scheme is selected, the result of the last select statement, which is not assigned to any variable, is used as the step output. The structure of the query result must match the structure of the output schema.

Data checkpoint column

The data checkpoint column is a column (field), from which the platform takes the last row value after each executed task run and stores it as a Data checkpoint. The data checkpoint value can be used in the SQL statements to control, which data should be processed in the next run. You can refer to the value using the predefined variable @DataCheckpoint. Example of use: processing data in cycles, where every cycle processes only a subset of the entire set due to the total size. If you use e.g. record ID as a data checkpoint column, the platform will store after each cycle the last processed ID from the data subset processed by the task run. If your statement is written in a way that will evaluate the value in data checkpoint against the IDs of the records in the data set, you can ensure this way, that only not processed records will be considered in the next task run.

Debug script enabled.

Input & Output Schema

Input

Input schema is optional

Output

Output schema is optional

Release notes

3.3.2

  • Connection and commands timeout is respecting task step timeout.

3.3.1

  • Fixed processing sensitive errors.

3.3.0

  • @EndpointTokenID, @EndpointTokenName and @DataCheckpoint variables available to use
  • Independence of Connection string and SQL Statement configurations.

3.2.5

  • Updated package binaries because of changes in another included connector.

3.2.1

  • Fix processing apostrophe char on input data (column type: string, Json, Base64, Complex schema).

3.2.0

  • Implemantation of debug script generating.

3.1.9

  • Updated package binaries because of changes in another included connector.

3.1.4

  • Updated package binaries because of changes in another included connector.

3.1.3

  • Fixed processing complex input schema.

3.1.2

  • Fixed shared nuget package versions.

3.1.1

  • Fixed right processing of nullable properties.

Was this page helpful?

Privacy Policy

©Integray 2024

Privacy Policy