The PostgreSQL Execc component allows you to execute SQL statements against PostgreSQL to update/delete data.
It supports PostgreSQL 11 and above.
Output payload
| Output Payload | Description |
|---|---|
cv.Payload |
Pass LastInsertId or RowsAffected depending on the SQL statement executed in addition to the input payload |
LastInsertId is returned if the SQL statement executed was an INSERT statement and the ID generated by the DB can be retrieved.
RowsAffected returns the number of records affected by the SQL statement executed. For example, if it was a DELETE statement, the number of rows deleted is returned.
h3. Component property
| Property Name | Description |
|---|---|
| SQL | Specify SQL statement to execute |
| Bulk Mode | Select whether to use bulk mode for SQL statement execution |
| Bulk Size | Specify the size if bulk mode is used |
| 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 must use a placeholder in the SQL statement to be executed to use the value of cv.Playload in the SQL statement.
UPDATE table1 SET name='update name' WHERE id=?
The value of cv.Payload is used for the placeholder specified in cv. When cv.Payload contains 1, the record with id 1 is updated.
More than one placeholder may be specified. For example:
UPDATE table1 SET name=$1 WHERE id=$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 here for how to use it.
Need more help with this?
Join our slack community for help