Blog

User-Defined Set-Returning Functions in Doltgres

Jason Fulghum
6 min read

Doltgres is a PostgreSQL-compatible database built on top of the Dolt version-controlled storage engine. That means you get familiar Postgres SQL semantics (e.g. schemas, joins, indexes, views, transactions, functions) combined with Git-style branching, diffing, merging, and time-travel queries on your data.

Doltgres is currently in Beta and moving towards a 1.0 release as we continue to add more coverage of Postgres features.

User-Defined Set-Returning Functions in Doltgres#

As we build support in Doltgres for all of PostgreSQL’s features, one large area is user-defined functions, particularly for the PL/pgSQL procedural language. We’ve come a long way with user-defined function support already, but there are still more features to support before we can be confident the vast majority of customers’ Postgres functions will work with Doltgres. Today, we’re excited to announce initial support for user-defined, set-returning functions.

Set-returning functions are identified by a function signature that contains either RETURNS TABLE or RETURNS SETOF. In this blog post, we’ll explain both syntaxes and show examples using the PL/pgSQL procedural language.

Quick Refresher on User-Defined Functions#

Most user-defined functions return a single scalar value. For example, the function signature may declare something like RETURNS int or RETURNS text.

Here’s an example of a very simple user-defined function that takes an integer and returns it after incrementing it by one:

CREATE FUNCTION add_one(x int)
RETURNS int
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN x + 1;
END
$$;

We can execute this new function by using it in a SQL statement and passing in a parameter:

SELECT add_one(5);

As expected, this returns a single value in a result set:

add_one 
---------
       6
(1 row)

Set-Returning Functions#

In addition to returning a single, scalar value, user-defined functions in Postgres can also return multiple results. Set-returning functions can return a set of records, and each record may have multiple fields. This allows user-defined functions to start to act more like a parameterized view or table. In this initial release, Doltgres doesn’t yet support joining these results sets with other tables, views, or function results, but this support will be coming soon.

One of the reasons why set-returning functions are useful is because they allow you to package up logic that produces relational data so that it can be easily reused. Callers can pass in parameters to control the results that are returned, without having to know the underlying logic that produces the results. This means the logic can be changed or migrated with no disruption to callers, so long as the function still fulfills the same contract. As queries and the surrounding logic get more complicated, being able to easily reuse these functions without having to understand the underlying details is a powerful way to eliminate duplication of your SQL queries, make functionality easier for other groups to access, and make your SQL queries more maintainable.

RETURNS TABLE (...)#

Let’s look at the first form of set-returning, user-defined functions. In this form, the function signature specifies RETURNS TABLE (...), and lists the field names and types in parentheses to define the custom, anonymous record type that the function returns.

Let’s look at a concrete example. First let’s create a couple tables and insert some sample data:

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name TEXT
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT,
    amount INT,
    created_at TIMESTAMP
);

INSERT INTO customers VALUES (1, 'John'), (2, 'Jane');
INSERT INTO orders VALUES (1, 1, 100, NOW()), (2, 2, 10, NOW());

Our sample data tracks customers and orders. Suppose we frequently need to query the order totals for each customer. Instead of repeating this aggregation everywhere, we can write a set-returning function that encapsulates that logic in an easy to call function interface:

CREATE FUNCTION customer_totals(IN min_amount INT)
RETURNS TABLE (
    c_id INT,
    n TEXT,
    t_spent INT
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT c.id,
           c.name,
           SUM(o.amount) AS total_spent
    FROM customers c
    JOIN orders o ON o.customer_id = c.id
    GROUP BY c.id, c.name
    HAVING SUM(o.amount) >= min_amount;
END;
$$;

Before we execute this function, let’s point out a few key details in its implementation.

First, notice the RETURNS TABLE syntax in its signature. This tells us that the function is going to return an anonymous composite type that contains three fields: an integer field named c_id, a text field named n and another integer field named t_spent. An anonymous composite type can’t be referenced by any name.

Next, notice that the LANGUAGE plpgsql line tells us this user-defined function is written with the PL/pgSQL procedural language.

Last, but not least, notice that this function uses RETURN QUERY <query> in the body of the function. RETURN QUERY will execute the specified query and queue up the results to be returned by the function. RETURN QUERY can be called multiple times in a function to add additional results to the returned records. The actual query we’re executing here is a straight-forward SELECT statement that projects a few values from the joined customers and orders tables, then groups them and filters the results based on the parameter min_amount.

Now that we understand the function, let’s query it with our sample data just to see what it returns:

SELECT customer_totals(5);

The results we get are:

customer_totals 
-----------------
 (1,John,100)
 (2,Jane,10)
(2 rows)

Notice that the results are returned as records of the anonymous type we defined in the function signature.

RETURNS SETOF#

Next, let’s look at the second form of set-returning functions. These functions use the RETURNS SETOF <composite_type> syntax and reference an existing composite type, instead of defining an anonymous composite type. This composite type is often a table’s row type – for every table you create, there is automatically a composite type of the same name created with the same fields.

Here’s a simple example of a set-returning, user-defined function using the RETURNS SETOF syntax:

CREATE FUNCTION orders_for_customer(cid INT)
RETURNS SETOF orders
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY 
    SELECT *
    FROM orders
    WHERE customer_id = cid
    ORDER BY created_at DESC;
END
$$;

Before we execute this function, let’s take a look at a few details in its implementation. The first thing to notice is that we define the return type of this function and specify that it is set-returning by using RETURNS SETOF orders. The composite type being returned is the composite type implicitly created when we created the orders table. Next, we can see this function is also written in PL/pgSQL, and again we see the use of RETURN QUERY to queue up the results of a query to be returned by the function.

One easy way to execute this function is to use it as an expression in a SELECT statement:

SELECT orders_for_customer(1);

When we run that statement, we get these results:

          orders_for_customer           
----------------------------------------
 (1,1,100,"2026-02-17 13:37:21.530028")
(1 row)

Again, we see a result set returned, with a set of records, and each record has the same fields as the orders table.

The query in this function is admittedly simple, but it still does a good job illustrating how user-defined functions can encapsulate queries and simplify the query syntax for anyone using that query. On its own, repeating the four lines from the query inside this function wouldn’t be a big deal, but like software, queries tend to get more complicated over time. Because this query is encapsulated in a user-defined function, a central team could extend this query, for example to add additional filtering to remove test data, or to process fields with additional logic (e.g. lower-casing certain fields). If this query were copy-pasted all over the codebase, it would be much harder to keep all those copies in sync, but with a user-defined function, it can be updated in one spot and all references will automatically use the new logic.

Wrap-Up#

Set-returning functions are a powerful feature for writing user-defined functions. They let you encapsulate queries and procedural logic together in an easy-to-call, functional interface. This can be particularly helpful to eliminate duplicated SQL and centralize business logic. Doltgres now supports RETURNS TABLE and RETURNS SETOF to let you write set-returning functions. There’s still more work for user-defined functions and set-returning functions, such as supporting array return types and allowing joins on set-returning functions. We’ll continue to fill in these gaps as well as building out other Postgres and PL/pgSQL features.

If you run into any issues using Doltgres or a feature you need isn’t supported yet, we want to know about it so we can help! We prefer to prioritize our work based on what features customers are telling us they need, so please hit us up on GitHub with an issue or swing by our Discord server and chat with us. We’d love to hear about your experience with Doltgres and learn about what you want to build.

Blog