cockroachdb / cockroachdb/cockroach

plpgsql: implement OPEN cursor FOR EXECUTE for dynamic cursors

Open
#169,574 0 comments 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 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

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.