Re: postgresql how to duplicate rows in result.

For my enlightenment, why use LATERAL here? I get the same result with a
simple CROSS JOIN (though overall I like the clever solution).

Cheers,
Steve

On Thu, Feb 16, 2017 at 12:11 AM, Alessandro Baggi <
alessandro(dot)baggi(at)gmail(dot)com> wrote:

> Il 15/02/2017 19:11, Alessandro Baggi ha scritto:
>
>> Il 14/02/2017 21:51, Merlin Moncure ha scritto:
>>
>>> On Tue, Feb 14, 2017 at 12:36 PM, Merlin Moncure <mmoncure(at)gmail(dot)com>
>>> wrote:
>>>
>>>> On Tue, Feb 14, 2017 at 12:04 PM, Alessandro Baggi
>>>> <alessandro(dot)baggi(at)gmail(dot)com> wrote:
>>>>
>>>>> Hi list,
>>>>> sorry for my english, I will try to example as well. I've a query
>>>>> that joins
>>>>> multiple tables and return a result like:
>>>>>
>>>>> id,customers,phone,code,number
>>>>> 1 , aaaaaaaa,33333,123 , 2
>>>>> 2 , aassdsds,33322,211 , 1
>>>>> 3 , oooooooo,21221,221 , 1
>>>>>
>>>>>
>>>>> I need, where "number" field is > 1, to duplicate the row * N(number
>>>>> field
>>>>> value) with a result like this:
>>>>>
>>>>> id,customers,phone,code,number
>>>>> 1 , aaaaaaaa,33333,123 , 2
>>>>> 1 , aaaaaaaa,33333,123 , 2
>>>>> 2 , aassdsds,33322,211 , 1
>>>>> 3 , oooooooo,21221,221 , 1
>>>>>
>>>>> How I can accomplish to this problem?
>>>>>
>>>>
>>>> SELECT * FROM foo CROSS JOIN LATERAL (1,number);
>>>>
>>>> :-D
>>>>
>>>
>>> oops -- copy/paste error
>>>
>>> SELECT * FROM foo CROSS JOIN LATERAL generate_series(1,number);
>>>
>>> merlin
>>> .
>>>
>>> Hi Merlin,
>> I've tried your suggested code and with cross join and generate_series I
>> can generate multiple row. There is a way to put as second args a column
>> values? I've tried to put "table.number" column values but I got
>> "generate_series() does not exists". Inserting a simple int like 5 I get
>> 5 results for each row.
>>
>> I've searched on google but can't find a valid example.
>>
>>
>>
>> Thanks in advance.
>>
>>
>> Hi Merlin,
> I've solved my problem (passing column as number) using a cast
> generate_series(1,table.number::int)
>
> thanks to all for answart.
>
> SOLVED
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>