hasura / hasura/graphql-engine

Support `returns Table(x type, y type)` custom functions

Open
#8,222 7 comments 5 reactions 0 assignees View on GitHub
k/enhancement
Dominant language
TypeScript
Stars
32.1k
Forks
3k
PR merge metrics
PR metrics pending

Description

Right now, functions must return a `setof ` such as:

```
CREATE FUNCTION good_things() RETURNS SETOF things AS $$
SELECT * FROM things WHERE good = true;
$$ LANGUAGE sql STABLE;
```

The problem is that the table `things` needs to already exist. Would be great if you could also specify a dynamic table as a return type too, such as:

```
CREATE FUNCTION better_things() RETURNS TABLE (key int, info text, retrieved_at timestamptz) AS $$
SELECT id as key, data as info, now() as retrieved_at FROM things WHERE good = true;
$$ LANGUAGE sql STABLE;
```

Doing so would add flexibility on the type of data returned, such as adding additional columns or hiding other columns. Furthermore, the `RETURNS TABLE` option provides an easy way to work with `RETURN NEXT`:

```
CREATE FUNCTION the_best_things() RETURNS TABLE (key int, info text, retrieved_at timestamptz) AS $$
BEGIN
FOR thing IN SELECT * FROM things WHERE good = true LOOP
-- You can do some imperative stuff in this block now.
key := thing.id;
info := thing.data;
retrieved_at := now();

PERFORM pg_notify('thing-' || key, 'retrieved_at: ' || extract(epoch from retrieved_at));

RETURN NEXT;
END LOOP;
END;
$$ LANGUAGE plpgsql STABLE;
```

Interested in any thoughts.

Contributor guide

Open the contributing guide

Research direction

The issue names no files, tests, or entry points. Start by locating the PostgreSQL custom-function support and its handling of SETOF return types; done should mean accepting RETURNS TABLE definitions and making their declared columns available through the resulting API.

Written by the indexing model from the issue text.

Assessment

Tech stack
postgresql
Domain
backend-api-design, databases
Issue type
Feature
Difficulty
5/5
Estimated time
Over a week
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
30/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.