cockroachdb / cockroachdb/cockroach

sql: allow computed columns using aggregates on SRFs

Open
#95,183 0 comments 0 reactions 0 assignees View on GitHub
C-enhancement T-sql-queries
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.