PostgreSQL: Set-returning function with single value in plpython3u

Question

I want to write a plpython3u function in PostgreSQL that returns a set of texts. I've stumbled across a conundrum that looks strange to me. As per the manual, I can do the following:

drop schema if exists X cascade;
create schema X;

create type X.greeting AS (
  who text
  );

create function X.greet( how text )
  returns setof X.greeting language plpython3u as $$
  for who in [ 'World', 'PostgreSQL', 'PL/Python' ]:
    yield ( who, )
  $$;

This is a Python function that returns a set of rows with single texts; so much works, and I do get the expected output:

select X.greet( 'helo' );

    greet     
--------------
 (World)
 (PostgreSQL)
 (PL/Python)
(3 rows)

select * from X.greet( 'helo' );

    who     
------------
 World
 PostgreSQL
 PL/Python
(3 rows)

So far so good. However, I do not want to write a table definition for the purpose, I'd like to use setof record instead, like in this example (which happens to use integers, but still):

create function X.pairs_of_integers_A( out integer, out integer )
  returns setof record language plpython3u as $$
  return [ ( 12, 24, ), ( 36, 48, ), ( 60, 72, ) ]
  $$;

create function X.pairs_of_integers_B( out integer, out integer )
  returns setof record language plpython3u as $$
  for pair in [ ( 12, 24, ), ( 36, 48, ), ( 60, 72, ) ]:
    yield pair
  $$;

select * from X.pairs_of_integers_A();
select * from X.pairs_of_integers_B();

 column1 | column2 
---------+---------
      12 |      24
      36 |      48
      60 |      72
(3 rows)

 column1 | column2 
---------+---------
      12 |      24
      36 |      48
      60 |      72
(3 rows)

Now we come to the interesting part. Generalizing from the above, one or more formulations of the below should be correct: to return a set of single values, either return a list of Python tuples, a list of Python numbers, or else iterate either over tuples with single values or over single values:

create function X.single_integers_A( out integer )
  returns setof record language plpython3u as $$
  return [ ( 12, ), ( 36, ), ( 60, ), ]
  $$;

create function X.single_integers_B( out integer )
  returns setof record language plpython3u as $$
  return [ 12, 36, 60, ]
  $$;

create function X.single_integers_C( out integer )
  returns setof record language plpython3u as $$
  for n in [ ( 12, ), ( 36, ), ( 60, ), ]
    yield n
  $$;

create function X.single_integers_D( out integer )
  returns setof record language plpython3u as $$
  for n in [ 12, 36, 60, ]
    yield n
  $$;

Turns out none of the above even compile, they all cause the SQL parser to throw up with function result type must be integer because of OUT parameters. Since I the SQL parser does not look inside Python functions, this leads me to suspect that—

It is not possible, in PostgreSQL, to define a function with both returns setof record and a single out parameter; instead, the output type must always be defined as a table (or by similar means).

Can anyone please correct me? It would be really annoying if this should turn out to be true. Surely I have made a mistake somewhere?

Answer 1

You should return just set of integers:

create or replace function x.single_integers()
returns setof integer language plpython3u as $$
    return [ 12, 36, 60 ]
$$;

select * from x.single_integers();

 single_integers 
-----------------
              12
              36
              60
(3 rows)    

Per the documentation (emphasis added):

The key point here is that you must write RETURNS SETOF record to indicate that the function returns multiple rows instead of just one. If there is only one output parameter, write that parameter's type instead of record.