cockroachdb / cockroachdb/cockroach
plpgsql: implement RETURN QUERY EXECUTE for dynamic set-returning functions
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
**Is your feature request related to a problem? Please describe.**
PostgreSQL's PL/pgSQL allows set-returning functions to return rows from
a dynamically-constructed SQL string via `RETURN QUERY EXECUTE`:
```sql
CREATE FUNCTION list_rows(tbl text) RETURNS SETOF some_type
LANGUAGE plpgsql AS $$
BEGIN
RETURN QUERY EXECUTE format('SELECT * FROM %I', tbl);
END;
$$;
```
CockroachDB does not support this. The PL/pgSQL parser detects the
`EXECUTE` keyword following `RETURN QUERY` and rejects it with an
unimplemented error labeled with the feature string
`return dynamic sql query`:
- [`pkg/sql/plpgsql/parser/plpgsql.y:1183-1196`](https://github.com/cockroachdb/cockroach/blob/master/pkg/sql/plpgsql/parser/plpgsql.y#L1183-L1196)
```go
return_query:
{
if plpgsqllex.(*lexer).peekForExecute() {
// Advance the lexer by one token so that the error correctly points to
// the EXECUTE keyword.
plpgsqllex.(*lexer).Advance(1)
return unimplemented (plpgsqllex, "return dynamic sql query")
}
...
}
```
The non-dynamic form (`RETURN QUERY `) is supported via
the work in #105240. This issue tracks the dynamic form specifically,
and is part of the broader dynamic-SQL effort tracked by #115300.
Telemetry from the unimplemented-feature pull (week of 2026-04-25)
shows ~36K occurrences of `unimplemented.return dynamic sql query`
across 3 clusters. Because the parser uses the plain
`unimplemented(feature)` helper instead of one tied to an issue
number, the telemetry bucket is just the feature string and does
not link to any tracking issue today; once this issue exists, the
parser call site should be migrated to a helper that records the
issue number (analogous to `unimplementedWithIssue` in the SQL
parser at [`pkg/sql/parser/sql.y:75`](https://github.com/cockroachdb/cockroach/blob/master/pkg/sql/parser/sql.y#L75)),
so the telemetry bucket becomes `unimplemented.#` and
correlates with the rest of the issue tracker.
**Describe the solution you'd like**
Implement `RETURN QUERY EXECUTE [USING ...]` in the PL/pgSQL builder,
matching PostgreSQL semantics:
- Parse the dynamic SQL string and any `USING` parameters.
- At runtime, evaluate the string, plan the resulting query, bind the
parameters, and append all returned rows to the function's result
set (same effect as static `RETURN QUERY` but with a dynamic plan).
- Errors from the dynamic SQL surface with the same handling as other
PL/pgSQL routine errors.
Related: #115300 (parent: plpgsql Dynamic SQL)
Epic CRDB-48117
Jira issue: CRDB-63536
Contributor guide
Assessment
This issue has not been assessed yet.