cockroachdb / cockroachdb/cockroach

plpgsql: return different types for a RECORD-returning PLpgSQL function

Open
#115,384 1 comment 0 reactions 0 assignees View on GitHub
A-sql-plpgsql C-enhancement docs-done docs-known-limitation P-3 T-sql-queries
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

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.