cockroachdb / cockroachdb/cockroach

plpgsql: implement RETURN QUERY EXECUTE for dynamic set-returning functions

Open
#169,571 1 comment 0 reactions 0 assignees View on GitHub
A-sql-pgcompat A-sql-plpgsql A-sql-routine C-enhancement O-agent T-sql-queries
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.