hasura / hasura/graphql-engine
Support `returns Table(x type, y type)` custom functions
- 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
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