cockroachdb / cockroachdb/cockroach

sql/plpgsql: support INTO [STRICT] clause in dynamic EXECUTE

Open
#172,402 0 comments 0 reactions 0 assignees View on GitHub
A-sql-plpgsql A-sql-routine C-enhancement O-agent T-sql-foundations
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.