cockroachdb / cockroachdb/cockroach
sql/plpgsql: support INTO [STRICT] clause in dynamic EXECUTE
- 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.**
PL/pgSQL's `EXECUTE ... INTO target` captures the first row of a
dynamically-constructed query's result into PL/pgSQL variables, the
dynamic-SQL counterpart to static `SELECT ... INTO`. CockroachDB's dynamic
`EXECUTE` support (#172401) explicitly rejects the `INTO` clause today.
**Describe the solution you'd like**
- Change `DynamicExecute.Target` from a single `Variable` to `[]Variable`,
matching the static `Execute.Target` field, so `EXECUTE ... INTO var1,
var2, var3` (multi-column capture) is supported.
- Resolve `INTO` target names as proper variable references instead of a
raw expression string (parser TODO at `lexer.go:244`).
- Fix the `EXECUTE ... USING ... INTO ...` clause-ordering parse bug: the
`USING` expression-list loop does not break on encountering `INTO`, so
the `INTO` target(s) get misparsed as additional `USING` parameters
instead of terminating the `USING` list.
- After executing the dynamic statement, assign the first result row to
the target variables using the same row-count handling as static
`SELECT INTO`: zero rows assign `NULL`s (or raise `no_data_found` under
`STRICT`); extra rows are silently ignored (or raise `too_many_rows`
under `STRICT`).
- `FOUND` must not be set by plain `EXECUTE` (PostgreSQL only sets it for
`FOR ... IN EXECUTE` and `RETURN QUERY EXECUTE`) — no dependency on
`GET DIAGNOSTICS` (#117410).
**Describe alternatives you've considered**
N/A — this restores parity with PostgreSQL's `exec_stmt_dynexecute` INTO
handling, which CockroachDB's static `SELECT INTO` path already
implements and this can reuse.
**Additional context**
Part of the dynamic-SQL EXECUTE effort tracked in #169581. Builds on basic
`EXECUTE` support (#172401).
Epic CRDB-48117
Jira issue: CRDB-65638
Contributor guide
Research direction
Start with DynamicExecute.Target and the parser TODO at lexer.go:244, then compare them with static Execute.Target and CockroachDB's existing static SELECT INTO path. Trace the USING expression-list handling and dynamic execution result assignment. Done means multi-variable INTO, correct USING/INTO ordering, PostgreSQL-compatible zero/extra-row and STRICT behavior, and no FOUND update for plain EXECUTE.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, sql
- Domain
- databases
- Issue type
- Feature
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 58/100