Support joins on functions that return records
- 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
Assessment
This issue has not been assessed yet.