Postgre SQL v2

PostgreSQL is a relational database platform used for transactional systems, reporting workloads, and application backends.

The Flowgear Postgre SQL Node runs parameterized queries, executes stored procedures, and upserts JSON objects in a PostgreSQL database.

Revision History

0.0.0.3 - Initial release.
0.0.0.5 - Optimized template discovery.
0.0.0.6 - Updated the Node icon.
0.0.0.7 - Added blank-query and stored-procedure templates.
0.0.0.8 - Improved Workflow parameter and legacy timestamp handling.
0.0.0.11 - Updated query, stored-procedure, and upsert templates and response contracts.

Connection

Use the Connection to configure the PostgreSQL server, credentials, and SSL settings.

Property Type Description
Server String The name or IP address of the server hosting the PostgreSQL instance.
Port Integer The PostgreSQL server port. The default is 5432.
Database String The name of the database to connect to.
Username String The PostgreSQL username used for the Connection.
Password Masked The PostgreSQL password used for the Connection.
SslMode Enum Determines the SSL configuration. Options include Disable, Allow, Prefer, Require, VerifyCA, and VerifyFull.
Trust Server Certificate Boolean When true, the Connection trusts the server certificate without validating it.
Connection Timeout Integer The time in seconds to wait while establishing a Connection. The minimum and default value is 15.
Enable Detailed Responses Boolean When true, error responses include more detail.
Enable Legacy Timestamp Behavior Boolean When true, the Query and Run Stored Procedure Methods enable the timestamp behavior used before Npgsql 6.0 and disable DateTime infinity conversions.

Setup Notes

Enter Server, Database, and Username, then configure Password and the SSL Properties required by your PostgreSQL server. Test the Connection after saving it.

Enable Trust Server Certificate only when your environment requires it. Use VerifyCA or VerifyFull for SslMode when the server certificate must be validated.

Methods

The Postgre SQL Node exposes Methods for running SQL queries, calling stored procedures, and upserting rows into a table.

Query

Executes a query against a PostgreSQL database and returns the provider result rows.

Parameter Type Description
Connection Connection The Postgre SQL Connection.
Query String The SQL statement to execute. Use @-prefixed placeholders and matching CustomParameters keys for external values.
CustomParameters Object Optional named SQL values. Every key must begin with one @, for example @CustomerId. Unused keys are allowed.
Return Type Description
Items Array The rows returned by the query. Each row is emitted as an object with its provider column names.

Select Blank Query for a runnable named-parameter example. The other query templates represent tables available in the configured database and include the table's columns in the Items schema.

Run Stored Procedure

Executes a stored procedure using a generated CALL statement. Select a stored-procedure template from the configured database to obtain the statement, input Properties, and output schema for a specific procedure overload.

Parameter Type Description
Connection Connection The Postgre SQL Connection.
QueryStatement String The generated CALL statement. Keep input values in CustomParameters instead of inserting them into SQL text.
CustomParameters Object Optional named procedure values. Every key must begin with one @. The selected template creates a Property for each input and input/output parameter.
Return Type Description
Items Array The procedure result rows. Each item includes the output values and the Flowgear response metadata.

PostgreSQL procedure templates place input and input/output parameters under CustomParameters. An output-only argument is represented by NULL in the generated statement, so it does not require an input value. The statement also includes explicit PostgreSQL type casts to select the intended procedure overload.

Each returned item includes Flowgear.IsSuccess, Flowgear.Message, and Flowgear.Request. A procedure without output arguments returns one success item so that the Workflow can confirm completion.

To call a procedure from a Workflow:

  1. Add this Node and select Run Stored Procedure.
  2. Select the database Connection and the required procedure template.
  3. Review the generated QueryStatement and map each input under CustomParameters.
  4. Run with a small input appropriate to the procedure, then inspect Items and any changes the procedure made in the database.

For example, a procedure parameter named customer_id has a template input named @customer_id. Map the source customer ID to that Property and retain the placeholder in the generated statement. The procedure must already exist in the selected database; the Node does not create it.

Upsert Table

Inserts each input object into a PostgreSQL table or updates the existing row when the configured conflict key matches.

Parameter Type Description
Connection Connection The Postgre SQL Connection.
Table Name String The table name, optionally schema-qualified and quoted.
Key Fields String Exact, comma-separated column names used as the PostgreSQL conflict key.
Items Array The objects to upsert. Each Property name must exactly match a column in the selected table.
Return Type Description
Response Array One response per input item, containing RowsAffected and the Flowgear response metadata.

The Node validates the table and performs the first write when the Method starts. A failure on the first item stops the Method. For later items, PostgreSQL and input-validation failures produce failed responses and processing continues with the remaining items.

Use Flowgear.Request to identify the input object for each response. A successful response reports the number of affected rows in RowsAffected; a failed response reports 0 and provides the error in Flowgear.Message.

Usage Notes

  • Use SQL placeholders with CustomParameters instead of inserting external values into the SQL text.
  • Begin every CustomParameters key with exactly one @. Object and array values are sent to PostgreSQL as JSON text.
  • Query templates include Blank Query followed by templates for tables outside the pg_catalog and information_schema schemas. A table template selects up to ten rows by default through @Limit.
  • Stored-procedure templates list accessible procedures outside the PostgreSQL system schemas. Selecting a template reads its inputs and outputs without executing the procedure.
  • For Upsert Table, use exact table, column, and conflict-key names. Do not add spaces around the commas in Key Fields.
  • Upsert templates populate Key Fields from the table's primary key and describe Items from its current columns.
  • Omit a column from an upsert item when you want PostgreSQL to apply that column's database default.
  • Enable Enable Legacy Timestamp Behavior only when an existing Workflow depends on the Npgsql behavior used before version 6.0.

See also