Insert Query

Add Postgres Execute Function

Start by adding the Postgres Execute function. Give it a Result Name and set the Type to Insert.

Write the SQL Query

In the function configuration, write the SQL query you want to use for inserting data.

Assume we have a users table and want to insert a new record. The users table schema looks like this:

CREATE TABLE public.users (
  id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  name text NULL,
  email text NULL,
  age integer NULL,
  activated boolean NULL,
  updated timestamp without time zone NULL,
  CONSTRAINT users_pkey PRIMARY KEY (id)
);

Now, write the following query:

INSERT INTO users(name, email, age, activated) VALUES($1, $2, $3, $4)

Note that we use placeholders ($1, $2, etc.) instead of hardcoding values—this helps safely pass parameters.

Fill the Arguments

You can provide arguments in three ways:

1. Pass a List: Provide a list of values matching the placeholders in the SQL query.
2. Use a Literal List: Construct a new list using literals or variables.
3. Pipe Values: Use pipes to transform current input values using a custom function.

In this example, we'll use a literal list for simplicity. Make sure the Arguments As Literal option is enabled.

Tap the editor to open a dialog and define your list:

Here's an example of the list literal:

["$name", "$email", "$age", true]

We use variable references like $name and pass true directly for the activated field.

Return Affected Rows

To return the Affected Rows, make sure to enable the corresponding option.

Setup Dependency with Postgres Connect

Instead of passing a new *PostgresDb instance each time, inject it using the Registry.

Use the Postgres Connect function inside your setup function (e.g., SetupDependency). This function runs when the server starts, connects to Postgres, and registers the database connection as PostgresDb.

You can register it with any name, but make sure the Dependency Name input in Postgres Execute matches exactly. (By default, it's PostgresDb.)

Choose a caching strategy:

• Cache Once or Cache Lazy are usually sufficient inside API handlers.

Enable Force Unwrap to retrieve the result only if there’s no error. It's recommended to handle errors gracefully.

Use the Returned Result

The result returned is either Res<SqlRes> or SqlRes, depending on whether you unwrapped it.

Testing

Add Postgres Execute Function

Add the Postgres Execute function, give it a Result Name, and set the Type to Insert Return.

Write the SQL Query

Similar to the previous example, insert the data and use RETURNING to get the newly created record's ID:

INSERT INTO users(name, email, age, activated) VALUES($1, $2, $3, $4) RETURNING id

Again, we use placeholders to safely pass parameters.

Fill the Arguments

Fill in the required arguments:

["$name", "$email", "$age", true]

Provide the Return Type

Select Int64 as the return type for the inserted ID. In this example, we return only the ID, but you can return multiple fields. Like with a SELECT query, define a custom struct to handle the returned data.

Use the Returned Result

The result returned is either Res<SqlRes> or SqlRes, depending on whether you unwrapped it.

Testing

Add Postgres Execute Function

Add the Postgres Execute function, give it a Result Name, and set the Type to Insert Bulk.

Write the SQL Query

We’re inserting data into the users table using this query:

INSERT INTO users(name, email, age, activated) VALUES #PLACE_HOLDERS

Instead of individual placeholders, we use #PLACE_HOLDERS, which the platform replaces based on the batch size and column count.

You’ll also need to define:

• Batch Size: how many records to insert per query (e.g., 3)
• Placeholder Count: the number of columns per row (e.g., 4)

Fill the Arguments

In this example, we provide 3 values using a literal.

Testing

Once everything is set up, run the function to test the bulk insert.

Step 1: Add Postgres Execute Function

Add the Postgres Execute function, give it a Result Name, and set the Type to Insert Bulk Returns.

Step 2: Write the SQL Query

We're inserting data into the users table using the following query:

INSERT INTO users(name, email, age, activated) VALUES #PLACE_HOLDERS RETURNING id

Instead of individual placeholders ($1, $2, etc.), we use #PLACE_HOLDERS, which the platform automatically replaces based on the batch size and number of columns.

You'll also need to define:

• Batch Size: the number of records to insert per query (e.g., 3)
• Placeholder Count: the number of columns per row (e.g., 4)

Step 3: Fill the Arguments

In this example, we provide 3 sets of values using a literal list.

Step 4: Provide the Return Type

Select Int64 as the return type for the inserted IDs. In this example, we only return the id, but you can return multiple fields. Similar to a SELECT statement, define a custom struct to receive the returned data.

Step 5: Testing

Once everything is configured, run the function to test the bulk insert and ensure the returned data is correct.