Re: help with generation_series in pg10 - Mailing list pgsql-general

On 01/08/2018 05:25 PM, Márcio A. Sepp wrote:
> 
> Hi,
> 
> 
> In pg10 generation series doesn't work like in 9.5.
> Ex. in 9.5:
> z=# select generate_series(1, 10), generate_series(1, 5);
>   generate_series | generate_series
> -----------------+-----------------
>                 1 |               1
>                 2 |               2
>                 3 |               3
>                 4 |               4
>                 5 |               5
>                 6 |               1
>                 7 |               2
>                 8 |               3
>                 9 |               4
>                10 |               5
> (10 registros)
> 
> 
> so, in version 10 the same sql show different result set.

The reason why:

https://www.postgresql.org/docs/10/static/release-10.html#idm46428657945600

Change the implementation of set-returning functions appearing in a 
query's SELECT list (Andres Freund)

Set-returning functions are now evaluated before evaluation of scalar 
expressions in the SELECT list, much as though they had been placed in a 
LATERAL FROM-clause item. This allows saner semantics for cases where 
multiple set-returning functions are present. If they return different 
numbers of rows, the shorter results are extended to match the longest 
result by adding nulls. Previously the results were cycled until they 
all terminated at the same time, producing a number of rows equal to the 
least common multiple of the functions' periods. In addition, 
set-returning functions are now disallowed within CASE and COALESCE 
constructs. For more information see Section 37.4.8.


> z=# select generate_series(1, 10), generate_series(1,5);
>   generate_series | generate_series
> -----------------+-----------------
>                 1 |               1
>                 2 |               2
>                 3 |               3
>                 4 |               4
>                 5 |               5
>                 6 |
>                 7 |
>                 8 |
>                 9 |
>                10 |
> (10 registros)
> 
> 
> how can i have the same in pg10 as i have had in pg 9.x?

I went to section 37.4.8:

https://www.postgresql.org/docs/10/static/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-SET

I am not seeing a solution, but you might see something that would help you.

> 
> I need it to date type to...  if possible.
> 
> 
> --
> Att.
> Márcio A. Sepp
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com