cockroachdb / cockroachdb/cockroach

plpgsql: support referring to arguments by ordinals $1, etc

Open
#114,701 1 comment 0 reactions 0 assignees View on GitHub
A-sql-pgcompat A-sql-plpgsql C-cleanup docs-done docs-known-limitation O-qa P-3 T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

From PG [docs](https://www.postgresql.org/docs/current/plpgsql-cursors.html). Currently, this works in PG but fails in CRDB:
```sql
CREATE TABLE test (col text);
INSERT INTO test VALUES ('123');

CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
BEGIN
OPEN $1 FOR SELECT col FROM test;
RETURN $1;
END;
' LANGUAGE plpgsql;
```
```
ERROR: at or near "$": syntax error
SQLSTATE: 42601
DETAIL: source SQL:
BEGIN
OPEN $1 FOR SELECT col FROM test;
^
```

Giving the name to the argument and referring by name works in CRDB.

```sql
CREATE FUNCTION reffunc(arg1 refcursor) RETURNS refcursor AS '
BEGIN
OPEN arg1 FOR SELECT col FROM test;
RETURN arg1;
END;
' LANGUAGE plpgsql;
```

Jira issue: CRDB-33639

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.