cockroachdb / cockroachdb/cockroach
sql: allow computed columns using aggregates on SRFs
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
Some scalar computations are more easily written as aggregations on set-returning functions than as pure scalar expressions. It would be nice if we could allow these in computed columns. Here's an example from @hand-crdb:
```sql
CREATE TABLE t (pk UUID DEFAULT gen_random_uuid() PRIMARY KEY, j JSONB NOT NULL);
INSERT INTO t (j) VALUES ('{"a": 1}'), ('{"b": 2}'), ('{"c": "C1", "d": true, "f": 3}'), ('{"c": "C2", "e": "E2", "f": 4}');
SELECT pk, j, jsonb_object_keys(j) FROM t;
SELECT pk, j, string_agg(jsonb_object_keys(j), ' ') FROM t GROUP BY pk, j;
ALTER TABLE t ADD COLUMN k TEXT GENERATED ALWAYS AS (string_agg(jsonb_object_keys(j), ' ')::TEXT) STORED;
```
Note that PostgreSQL does not allow aggregations or set-returning functions in computed columns, and also does not allow aggregations on set-returning functions. CockroachDB allows the latter.
Jira issue: CRDB-23386
Contributor guide
Research direction
Start with the SQL example in the issue and compare the current computed-column and aggregate restrictions with CockroachDB's existing support for aggregates on set-returning functions. Use Jira issue CRDB-23386 for context; done means the shown generated-column statement is accepted with the intended aggregated value and covered by regression tests.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- sql
- Domain
- databases
- Issue type
- Feature
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100