Re: BUG #13317: Cannot select multiple columns from multiple rows returned from PL/Python function

On Tuesday, May 19, 2015, <cpburnz(at)gmail(dot)com> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 13317
> Logged by: Caleb P. Burns
> Email address: cpburnz(at)gmail(dot)com <javascript:;>
> PostgreSQL version: 9.3.6
> Operating system: Ubuntu 12.04.5
> Description:
>
> If I define a SQL function as:
>
> CREATE FUNCTION sql_test() RETURNS TABLE (a INTEGER, b INTEGER) AS $$
> SELECT 1, 2
> UNION ALL
> SELECT 3, 4
> $$ LANGUAGE sql IMMUTABLE ROWS 2;
>
> I can select the values from both columns:
>
> postgres=# SELECT (sql_test()).*;
> a | b
> ---+---
> 1 | 2
> 3 | 4
> (2 rows)
>
> I can also do the same for a PL/pgSQL function:
>
> CREATE FUNCTION plpgsql_test() RETURNS TABLE (a INTEGER, b INTEGER) AS $$
> BEGIN
> RETURN QUERY SELECT 1, 2 UNION ALL SELECT 3, 4;
> END
> $$ LANGUAGE plpgsql IMMUTABLE ROWS 2;
>
> postgres=# SELECT (plpgsql_test()).*;
> a | b
> ---+---
> 1 | 2
> 3 | 4
> (2 rows)
>
> If I try to do the same for a PL/Python (3u) function, the query will run
> for more than 5 or 10 minutes and never finish:
>
> CREATE FUNCTION plpython_yield_test() RETURNS TABLE (a INTEGER, b INTEGER)
> AS $$
> yield (1, 2)
> yield (3, 4)
> $$ LANGUAGE plpython3u IMMUTABLE ROWS 2;
>
> postgres=# SELECT (plpython_yield_test()).*;
> ^CCancel request sent
> Cancel request sent
> ERROR: canceling statement due to user request
>
>
> This appears to be a bug that selecting from multiple columns returned from
> a PL/Python function returning multiple rows does not work (never
> finishes).
>
>
Then don't do that. Seriously, don't do that. Ever. Even when it
"works" it isn't actually working. So, just don't do that.

If you can use LATERAL you should do so. Otherwise use the form:

with func_call (res) as (
Select func(...)
)
Select (func_call.res).* from func_call;

Put a raise notice in the pl/pgsql version of the function to see why.
Basically the function is evaluated once for each column being asked for.
Python is having issues with the function being invoked repeatedly probably
resulting in some kind of infinite recursion. This is likely a bug that
could be fixed but the "workaround" is the correct way to handle set
returning functions in the select. You must return the overall type first
and then explode the type. You should not explode the function itself.

David J.