Select Query (Postgres)

Example 1: Select Data

In this example, we'll use the Postgres Execute function with the Type set to Select to get data from a Postgres database.

Add Postgres Execute Function

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

Write the SQL Query

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

Assume we have a users table and want to retrieve x number of records. 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:

SELECT id, name, email, activated FROM users LIMIT $1

Fill the Arguments

Now provide the required arguments for the query. Since we're using LIMIT $1, we need to supply how many records to fetch. For example, if you want to retrieve 5 users:

[5]

Define a Model for Returned Values

Next, define a model (struct) that maps the returned SQL data to a usable object in the function.

We recommend defining models in reusable custom functions. This allows you to update a model in one place and automatically reflect changes across all references.

For simplicity, we’ll define the model inline in this example.

In the Model input:

Click Define to create a new struct.
Set a name, e.g., User.
Add your fields. These can include more than what's returned from the query, but must at least match the selected columns.

Map Struct Fields to SQL Query

After defining your model, map the fields to the selected SQL columns using the Field Mapper input.

The order here must match the order of columns in your SQL query:

["Id", "Name", "Email", "Activated"]

Finally, set the Expected Result Set to 5 to allocate a list with enough space to store the results efficiently.

Select

Use the Returned Result

The result is returned as either Res<SqlRes> or directly SqlRes if unwrapped.

It contains a Data field, which is a list of rows returned from the database.

To access a specific field, like the ID of the first user, use dot syntax:

selectRes1.Data.0.Id

To easily select nested fields, use the UI helper:

Click the Select button next to the selectRes1 variable.
Click the Data field, then manually enter an index (e.g. 0) in the input box.
Click the field name (e.g. Id) from the list that appears.

You can insert ! at the end to force unwrap the result and access the value directly without handling the Result wrapper.