cockroachdb / cockroachdb/cockroach
plpgsql: return different types for a RECORD-returning PLpgSQL function
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
In postgres, it is possible to define a RECORD-returning PLpgSQL function that returns different-typed expressions from different return statements:
```
postgres=# CREATE OR REPLACE FUNCTION f(n INT) RETURNS RECORD AS $$
BEGIN
IF n = 0 THEN
RETURN ROW(True);
ELSE
RETURN ROW(100);
END IF;
END
$$ LANGUAGE PLpgSQL;
CREATE FUNCTION
postgres=# SELECT f(0);
f
-----
(t)
(1 row)
postgres=# SELECT f(1);
f
-------
(100)
(1 row)
```
This is because postgres' RECORD type behaves differently than in CRDB - its internal type is more dynamic, and elements can only be accessed without a column definition list that defines the expected type:
```
postgres=# SELECT * FROM f(0);
ERROR: a column definition list is required for functions returning "record"
LINE 1: SELECT * FROM f(0);
^
postgres=# SELECT * FROM f(0) AS foo(x TEXT);
ERROR: returned record type does not match expected record type
DETAIL: Returned type boolean does not match expected type text in column 1.
CONTEXT: PL/pgSQL function f(integer) while casting return value to function's return type
postgres=# SELECT * FROM f(1) AS foo(x TEXT);
ERROR: returned record type does not match expected record type
DETAIL: Returned type integer does not match expected type text in column 1.
CONTEXT: PL/pgSQL function f(integer) while casting return value to function's return type
```
This issue tracks adding support for this behavior.
Jira issue: CRDB-34778
Contributor guide
Assessment
This issue has not been assessed yet.