Re: [GENERAL] postgresql how to duplicate rows in result.

Il 14/02/2017 21:51, Merlin Moncure ha scritto:
> On Tue, Feb 14, 2017 at 12:36 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
>> On Tue, Feb 14, 2017 at 12:04 PM, Alessandro Baggi
>> <alessandro.baggi@gmail.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.