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?

yes - function either has out parameters and returns void, or returns something and has no out parameters I think — Vao Tsun, Nov 09 '17 at 16:54
https://www.postgresql.org/docs/current/static/plpgsql-control-structures.html — Vao Tsun, Nov 09 '17 at 17:05
1) thanks for comments 2) relevant link is https://www.postgresql.org/docs/current/static/plpython-data.html#idm46428683116992 3) the docs say, "Set-returning functions with OUT parameters (using RETURNS SETOF record) are also supported."—so yes, out-parameters + returns-setof-record is a thing. — John Frazer, Nov 09 '17 at 17:10

klin · Accepted Answer · 2017-11-09T17:37:56.913

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.

edited Nov 09 '17 at 17:37

answered Nov 09 '17 at 17:14

klin

112,967
15
204
232

I'm relieved I found a lacuna in my understanding of SQL instead of a bug / an oversight / an inconsistency in SQL. – John Frazer Nov 09 '17 at 17:58

PostgreSQL: Set-returning function with single value in plpython3u

1 Answers1