All geek questions in one place

Split function returns a record in several columns

The basic Postgres tutorial has an example with OUT options, for example:

 create or replace function hi_lo(a numeric, b numeric, c numeric, OUT hi numeric, OUT lo numeric) as $$ begin hi := greatest(a, b, c); lo := least(a, b, c); end; $$ language plpgsql;

Then the results look like

 select hi_lo(2, 3, 4); -- returns one column, "hi_lo" with value "(4, 2)". select * from hi_lo(2, 3, 4); -- returns two columns, "hi" / 4 and "lo" / 2.

But suppose you want to execute a function in columns that come from a join, and that you don’t have access to change a function or use an alternative function? For example, using some data about the toy:

 select hi_lo(a.actor_id, length(a.name), ma.movie_id) from actors a join movies_actors ma on a.actor_id = ma.movie_id limit 10;

returns the results in a single column "hi_lo" have 2-tuple values.

The request wrapper in parentheses and an attempt to select * from it do not change the output format. So

 select * from ( select hi_lo(a.actor_id, length(a.name), ma.movie_id) from actors a join movies_actors ma on a.actor_id = ma.movie_id limit 10; ) rr

does not affect the form of the result.

The following attempt results in an error: "The subquery should return only one column"

 select ( select * from hi_lo(a.actor_id, length(a.name), ma.movie_id) ) from actors a join movies_actors ma on a.actor_id = ma.movie_id limit 10;

Finally, I also tried unnest , but it gives an argument type error because the values ​​of the tuple are not treated as arrays.

How can you get multiple columns in the output if you cannot move the function evaluation to the from section?

+3
source share
1 answer

In Postgres 9.3 or later, this is best solved with a LATERAL join:

 SELECT * FROM actors a JOIN movies_actors ma on a.actor_id = ma.movie_id LEFT JOIN LATERAL hi_lo(a.actor_id, length(a.name), ma.movie_id) x ON true LIMIT 10;

Avoids re-evaluating the function (for each output column - the function must be called for each input row in any case).
LEFT JOIN LATERAL ... ON true to avoid deleting rows on the left side if the function does not return a row:

Follow the steps in your comment :

only extended columns created by function call

 SELECT x.* -- that all! FROM actors a JOIN movies_actors ma on a.actor_id = ma.movie_id LEFT JOIN LATERAL hi_lo(a.actor_id, length(a.name), ma.movie_id) x ON true LIMIT 10;

But since you do not need other columns, you can simplify:

 SELECT x.* FROM actors a JOIN movies_actors ma on a.actor_id = ma.movie_id , hi_lo(a.actor_id, length(a.name), ma.movie_id) x LIMIT 10;

This is an implicit CROSS JOIN LATERAL . If a function can actually return “no string” occasionally, the result may be different: we do not get NULL values ​​for the strings, these strings are simply deleted - and LIMIT no longer counts them.

In older versions (or in general), you can also simply decompose a composite type with the correct syntax:

 SELECT *, ( hi_lo(a.actor_id, length(a.name), ma.movie_id) ).* -- note extra parentheses! FROM actors a JOIN movies_actors ma on a.actor_id = ma.movie_id LIMIT 10;

The disadvantage is that the function is evaluated once for each column in the function output due to the weakness of the Postgres query scheduler. It is better to move the call to a subquery or CTE and decompose the row type in an external SELECT . How:

 SELECT actor_id, movie_id, (x).* -- explicit column names for the rest FROM ( SELECT *, hi_lo(a.actor_id, length(a.name), ma.movie_id) AS x FROM actors a JOIN movies_actors ma on a.actor_id = ma.movie_id LIMIT 10 ) sub;

But you must name the individual columns and cannot leave with SELECT * unless you are okay with the row type as a result is redundant. Connected:

+3
source

Source: https://habr.com/ru/post/956913/

More articles:


All Articles