cockroachdb / cockroachdb/cockroach
plpgsql: implement OPEN cursor FOR EXECUTE for dynamic cursors
- 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 opening a cursor over a dynamically-
constructed SQL string via `OPEN ... FOR EXECUTE`:
```sql
CREATE FUNCTION dump_table(tbl text) RETURNS void AS $$
DECLARE
c refcursor;
BEGIN
OPEN c FOR EXECUTE format('SELECT * FROM %I', tbl);
-- ... FETCH from c ...
CLOSE c;
END;
$$ LANGUAGE plpgsql;
```
CockroachDB rejects this at parse time:
- [`pkg/sql/plpgsql/parser/plpgsql.y:1402-1405`](https://github.com/cockroachdb/cockroach/blob/master/pkg/sql/plpgsql/parser/plpgsql.y#L1402-L1405)
```go
| OPEN IDENT opt_scrollable FOR EXECUTE
{
return unimplemented(plpgsqllex, "cursor for execute")
}
```
The non-dynamic form (`OPEN c FOR `) is supported.
This issue tracks the dynamic-SQL form and is part of the broader
dynamic-SQL effort tracked by #115300. The error is reported under
the bare telemetry key `unimplemented.cursor for execute`, with no
link to a tracking issue.
**Describe the solution you'd like**
Implement `OPEN [SCROLL|NO SCROLL] FOR EXECUTE
[USING ]`:
- Evaluate the expression to a SQL string at OPEN time.
- Plan and bind the resulting query (with any `USING` parameters)
to the cursor, so subsequent `FETCH`/`MOVE`/`CLOSE` operate on
it the same way as a statically-defined cursor.
- Errors from planning the dynamic SQL surface as PL/pgSQL
exceptions in the usual way.
Migrate the parser site to an issue-linked unimplemented helper
referencing this issue once the work lands.
Related: #115300 (parent: plpgsql Dynamic SQL), #169571 (RETURN QUERY
EXECUTE), #169557 (split catch-all PL/pgSQL telemetry).
Epic CRDB-48117
Contributor guide
Assessment
This issue has not been assessed yet.