PostgreSQL Query

The PostgreSQL Query component allows you to execute query statements against PostgreSQL to retrieve data.
It supports PostgreSQL 11 and above.

Output payload

Output Payload	Description
cv.Payload	Outputs a record in the output format specified by the property for each record that matches the condition.

Example of output in array format

When table1 table has column1 (integer type) and column2 (string type), specifying `SELECT * FROM table1` in the SQL property and "array" in the output format property, the output payload is generated as follows.

Output Example
[1, "abc"]
[2, "def"]
[3, "hij"].

Example of output in object format

When table1 table has column1 (integer type) and column2 (string type), specifying `SELECT * FROM table1` in the SQL property and "object format" in the output format property, the output payload is generated as follows.

Output example
{"column1": 1, "column2": "abc"}
{"column1": 2, "column2": "def"}
{"column1": 3, "column2": "hij"}

Component properties

Property Name	Description
SQL	Specify SQL statement to execute
Output Format	Select array or object format
Host	Specify PostgreSQL host.
Port	Specify the PostgreSQL port.
Username	Specify a PostgreSQL username.
Password	Specify the PostgreSQL password.
Database	Specifies a database.
Timezone	Specify the timezone for the database
SSLMode	The encryption of the communication can be disable (no SSL/TLS connection), allow (first try a connection without SSL/TLS connection and if the connection fails, then try a SSL/TLS connection), prefer (first try a connection with SSL/TLS connection and if the connection fails, then try a connection without SSL/TLS connection), or require (try a connection with SSL/TLS connection). connection), prefer (first try an SSL/TLS connection and if it fails to connect, then try a non-SSL/TLS connection), require (make an SSL/TLS connection. (makes an SSL/TLS connection and verifies the server certificate if specified, but not if not specified), verify-ca (makes an SSL/TLS connection and verifies the server certificate), verify-full (verifies the SSL/TLS connection and the server certificate, and also verifies the server name)
Root Certificate	Specify the root certificate to use; if you are using AWS Aurora or RDS, you must use a server certificate issued by AWS. This will be the root certificate for that server certificate.

Placeholder

You can use a placeholder in the SQL statement to be executed to use the value of cv.Playload in the SQL statement.

SELECT * FROM table1 WHERE id=$1

$1 The value of cv.Payload is used for the placeholder specified in cv. When cv.Payload contains 1, the record with id 1 can be retrieved.
More than one placeholder can be specified.

SELECT * FROM table1 WHERE name=$1 AND age=$2

To specify values for more than one placeholder, specify the values in an array in cv.

["First Last", 20]

Text2SQL

Text2SQL is a function that allows you to generate SQL statements from the Japanese you enter in the prompt for the data you wish to retrieve using OpenAI's ChatGPT functionality.
Please refer to the Text2SQL Usage Guide in the Setup Guide for how to use it.