sql: a function returning SETOF RECORD with OUT params returns one row

[DrewKimball]

It is possible to use RETURNS SETOF syntax to indicate that a user-defined function returns more than one row. This syntax is compatible with the RECORD return type:

root@localhost:26257/defaultdb> create function f() returns setof record language sql as $$ select t, t::text from generate_series(1, 10) g(t); $$;
CREATE FUNCTION

Time: 128ms total (execution 128ms / network 0ms)

root@localhost:26257/defaultdb> select f();
     f
-----------
  (1,1)
  (2,2)
  (3,3)
  (4,4)
  (5,5)
  (6,6)
  (7,7)
  (8,8)
  (9,9)
  (10,10)
(10 rows)

However, naming the elements of the record using OUT parameters causes CRDB to truncate the result:

root@localhost:26257/defaultdb> create function f(out x int, out y text) returns setof record language sql as $$ select t, t::text from generate_series(1, 10) g(t); $$;
CREATE FUNCTION

Time: 154ms total (execution 154ms / network 0ms)

root@localhost:26257/defaultdb> select f();
    f
---------
  (1,1)
(1 row)

Contrast this with Postgres, which returns all the rows:

postgres=# create function f(out x int, out y text) returns setof record language sql as $$ select t, t::text from generate_series(1, 10) g(t); $$;
CREATE FUNCTION
postgres=# select f();
    f
---------
 (1,1)
 (2,2)
 (3,3)
 (4,4)
 (5,5)
 (6,6)
 (7,7)
 (8,8)
 (9,9)
 (10,10)
(10 rows)

Jira issue: CRDB-40994

[blathers-crl]

Hi @DrewKimball, please add branch-* labels to identify which branch(es) this C-bug affects.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}