dolthub / dolthub/doltgresql

Support joins on functions that return records

Open
#2,341 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
Go
Stars
2.1k
Forks
73
Avg merge
1d 10h
Merged PRs (30d)
129

Description

User defined functions can return records, and Postgres allows joins on those results. Note that when a set returning function is invoked as a table function (e.g. `SELECT * FROM my_func()`), it returns the results as relational data, not as a set of records.

In Postgres, you can use the results from set returning functions to join on other data:
```sql
CREATE TABLE t (id INT PRIMARY KEY);
CREATE TABLE u (id INT PRIMARY KEY, val INT);

INSERT INTO t VALUES (1), (2);
INSERT INTO u VALUES (1, 10), (2, 20);

CREATE FUNCTION f(p_id INT)
RETURNS TABLE (id INT)
LANGUAGE SQL
AS $$
SELECT p_id;
$$;

SELECT x.id, u.val
FROM t, f(t.id) AS x, u
WHERE u.id = x.id
ORDER BY u.val;
```

In PostgreSQL-15, this returns:
```sql
id | val
----+-----
1 | 10
2 | 20
(2 rows)
```

In the current Doltgres version, it returns this error:
```sql
ERROR: table not found: t (errno 1146) (sqlstate HY000)
```

GMS provides a `TableFunctionWrapper` type that already wraps functions when they are used as a table function in a `FROM` clause. The main changes to enable joining on results of table functions are:
* Update the `sql.Function` interface (or another API) so it can report if a function is set-returning or not
* Update `TableFunctionWrapper` so that for set-returning functions, it unwraps the records
* Move the `RecordValue` type into GMS so that `TableFunctionWrapper` can access it
* Update the `sql.Function` interface (or another API) so that it can return it's schema

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.