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

2017-02-21 Thread Tom Lane 
Merlin Moncure  writes:
> On Thu, Feb 16, 2017 at 10:27 AM, David G. Johnston
>  wrote:
>> If it wasn't lateral the reference to number in "generate_series(1, number)"
>> would fail.

> huh -- I didn't know that!  Testing it out, all JOIN types imply
> LATERAL if the function call is tlist SRF style (which is pretty werid
> IMO)

Quite.  We wouldn't have done that, except we concluded that the SQL
spec requires it.  I don't recall all the details, but I think there
are specific function-like syntaxes (maybe only UNNEST?) that the spec
says act as if they're always LATERAL.  It seemed like it was less of
a wart to make that true for all function-in-FROM cases than just for
some of them.

> I tend to avoid optional words (with the important exception of
> AS for column list renames) but I think it's a good idea to disclose
> LATERAL in this case.

Agreed.  We do make rule printing show LATERAL explicitly when it's
active:

regression=# create view vv as select * from int8_tbl cross join 
generate_series(1,q1) g;
CREATE VIEW
regression=# \d+ vv
 View "public.vv"
 Column |  Type  | Collation | Nullable | Default | Storage | Description 
++---+--+-+-+-
 q1 | bigint |   |  | | plain   | 
 q2 | bigint |   |  | | plain   | 
 g  | bigint |   |  | | plain   | 
View definition:
 SELECT int8_tbl.q1,
int8_tbl.q2,
g.g
   FROM int8_tbl
 CROSS JOIN LATERAL generate_series(1::bigint, int8_tbl.q1) g(g);


regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2017-02-21 Thread Merlin Moncure 
On Thu, Feb 16, 2017 at 10:27 AM, David G. Johnston
 wrote:
> If it wasn't lateral the reference to number in "generate_series(1, number)"
> would fail.

huh -- I didn't know that!  Testing it out, all JOIN types imply
LATERAL if the function call is tlist SRF style (which is pretty werid
IMO)  I tend to avoid optional words (with the important exception of
AS for column list renames) but I think it's a good idea to disclose
LATERAL in this case.  It's a big clue to the reader what is going on
and the expanded form:

SELECT foo.* FROM foo CROSS JOIN LATERAL (SELECT a,b FROM func(foo.bar)) q;

... requires LATERAL to be explicitly stated.  This form is more
general since it can be cleanly used when func() returns more than one
column.

merlin


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2017-02-16 Thread David G. Johnston 
On Thu, Feb 16, 2017 at 9:16 AM, Steve Crawford <
scrawf...@pinpointresearch.com> wrote:

> For my enlightenment, why use LATERAL here? I get the same result with a
> simple CROSS JOIN (though overall I like the clever solution).
>
>
​To be explicit, I think.  CROSS JOIN function() implies lateral in the
implementation but you can always specify it if desired.  Like saying LEFT
OUTER JOIN instead of LEFT JOIN

If it wasn't lateral the reference to number in "generate_series(1,
number)" would fail.

David J.
​

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

2017-02-16 Thread Steve Crawford 
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.ba...@gmail.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 
>>> wrote:
>>>
 On Tue, Feb 14, 2017 at 12:04 PM, Alessandro Baggi
  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 , ,3,123 , 2
> 2 , aassdsds,33322,211 , 1
> 3 , ,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 , ,3,123 , 2
> 1 , ,3,123 , 2
> 2 , aassdsds,33322,211 , 1
> 3 , ,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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

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

2017-02-16 Thread Alessandro Baggi 

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 
wrote:

On Tue, Feb 14, 2017 at 12:04 PM, Alessandro Baggi
 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 , ,3,123 , 2
2 , aassdsds,33322,211 , 1
3 , ,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 , ,3,123 , 2
1 , ,3,123 , 2
2 , aassdsds,33322,211 , 1
3 , ,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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2017-02-15 Thread Alessandro Baggi 

Il 14/02/2017 21:51, Merlin Moncure ha scritto:

On Tue, Feb 14, 2017 at 12:36 PM, Merlin Moncure  wrote:

On Tue, Feb 14, 2017 at 12:04 PM, Alessandro Baggi
 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 , ,3,123 , 2
2 , aassdsds,33322,211 , 1
3 , ,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 , ,3,123 , 2
1 , ,3,123 , 2
2 , aassdsds,33322,211 , 1
3 , ,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.




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2017-02-14 Thread John McKown 
On Tue, Feb 14, 2017 at 2:50 PM, Merlin Moncure  wrote:

> ​
>
>
> IMO, lateral join (available as of 9.3) is faster and simpler.
>

​And, nicely, I've learned something new. I've never used a LATERAL join
before. Interesting.​



>
> merlin
>



-- 
"Irrigation of the land with sewater desalinated by fusion power is
ancient. It's called 'rain'." -- Michael McClary, in alt.fusion

Maranatha! <><
John McKown

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

2017-02-14 Thread Merlin Moncure 
On Tue, Feb 14, 2017 at 12:36 PM, Merlin Moncure  wrote:
> On Tue, Feb 14, 2017 at 12:04 PM, Alessandro Baggi
>  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 , ,3,123 , 2
>> 2 , aassdsds,33322,211 , 1
>> 3 , ,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 , ,3,123 , 2
>> 1 , ,3,123 , 2
>> 2 , aassdsds,33322,211 , 1
>> 3 , ,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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2017-02-14 Thread Merlin Moncure 
On Tue, Feb 14, 2017 at 12:42 PM, John McKown
 wrote:
> On Tue, Feb 14, 2017 at 12:04 PM, Alessandro Baggi
>  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 , ,3,123 , 2
>> 2 , aassdsds,33322,211 , 1
>> 3 , ,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 , ,3,123 , 2
>> 1 , ,3,123 , 2
>> 2 , aassdsds,33322,211 , 1
>> 3 , ,21221,221 , 1
>>
>> How I can accomplish to this problem?
>>
>> I'm using postgresql 9.3.15
>
>
> I don't have PostgreSQL 9.3.15. I am running 9.5.5. But I think the
> following recursive CTE should work for you.
>
> 
>
> tsh009=# \d baggi
>   Table "public.baggi"
>   Column   |  Type   | Modifiers
> ---+-+---
>  id| integer |
>  customers | text|
>  phone | text|
>  code  | integer |
>  number| integer |
>
> tsh009=# select * from baggi;
>  id | customers | phone | code | number
> +---+---+--+
>   1 |   | 3 |  123 |  2
>   2 | aassdsds  | 33322 |  211 |  1
>   3 |   | 21221 |  221 |  1
> (3 rows)
>
> tsh009=# with recursive multiple(k, id, customers, phone, code, number) as (
> select 1, id, customers, phone, code, number from baggi
> UNION ALL
> select m.k + 1, b.id, b.customers, b.phone, b.code, b.number from multiple
> AS m, baggi AS b  where m.id = b.id and m.k < b.number )
> select id, customers, phone, code, number from multiple order by id
> ;
>  id | customers | phone | code | number
> +---+---+--+
>   1 |   | 3 |  123 |  2
>   1 |   | 3 |  123 |  2
>   2 | aassdsds  | 33322 |  211 |  1
>   3 |   | 21221 |  221 |  1
> (4 rows)

IMO, lateral join (available as of 9.3) is faster and simpler.

merlin


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2017-02-14 Thread John McKown 
On Tue, Feb 14, 2017 at 12:04 PM, Alessandro Baggi <
alessandro.ba...@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 , ,3,123 , 2
> 2 , aassdsds,33322,211 , 1
> 3 , ,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 , ,3,123 , 2
> 1 , ,3,123 , 2
> 2 , aassdsds,33322,211 , 1
> 3 , ,21221,221 , 1
>
> How I can accomplish to this problem?
>
> I'm using postgresql 9.3.15


​I don't have PostgreSQL 9.3.15. I am running 9.5.5. But I think the
following recursive CTE should work for you.​



tsh009=# \d baggi
  Table "public.baggi"
  Column   |  Type   | Modifiers
---+-+---
 id| integer |
 customers | text|
 phone | text|
 code  | integer |
 number| integer |

tsh009=# select * from baggi;
 id | customers | phone | code | number
+---+---+--+
  1 |   | 3 |  123 |  2
  2 | aassdsds  | 33322 |  211 |  1
  3 |   | 21221 |  221 |  1
(3 rows)

tsh009=# with recursive multiple(k, id, customers, phone, code, number) as (
select 1, id, customers, phone, code, number from baggi
UNION ALL
select m.k + 1, b.id, b.customers, b.phone, b.code, b.number from multiple
AS m, baggi AS b  where m.id = b.id and m.k < b.number )
select id, customers, phone, code, number from multiple order by id
;
 id | customers | phone | code | number
+---+---+--+
  1 |   | 3 |  123 |  2
  1 |   | 3 |  123 |  2
  2 | aassdsds  | 33322 |  211 |  1
  3 |   | 21221 |  221 |  1
(4 rows)







> .
>
> thanks in advance.
>
> Alessandro.
>


-- 
"Irrigation of the land with sewater desalinated by fusion power is
ancient. It's called 'rain'." -- Michael McClary, in alt.fusion

Maranatha! <><
John McKown

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

2017-02-14 Thread Merlin Moncure 
On Tue, Feb 14, 2017 at 12:04 PM, Alessandro Baggi
 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 , ,3,123 , 2
> 2 , aassdsds,33322,211 , 1
> 3 , ,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 , ,3,123 , 2
> 1 , ,3,123 , 2
> 2 , aassdsds,33322,211 , 1
> 3 , ,21221,221 , 1
>
> How I can accomplish to this problem?

SELECT * FROM foo CROSS JOIN LATERAL (1,number);

:-D

merlin


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2017-02-14 Thread Melvin Davidson 
On Tue, Feb 14, 2017 at 1:04 PM, Alessandro Baggi <
alessandro.ba...@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 , ,3,123 , 2
> 2 , aassdsds,33322,211 , 1
> 3 , ,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 , ,3,123 , 2
> 1 , ,3,123 , 2
> 2 , aassdsds,33322,211 , 1
> 3 , ,21221,221 , 1
>
> How I can accomplish to this problem?
>
> I'm using postgresql 9.3.15.
>
> thanks in advance.
>
> Alessandro.
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

It would be immensely helpful if you provided the schema of tables involved
with original query.
In the meantime, I suggest you look into the use of UNION.

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

[GENERAL] postgresql how to duplicate rows in result.

2017-02-14 Thread Alessandro Baggi 

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 , ,3,123 , 2
2 , aassdsds,33322,211 , 1
3 , ,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 , ,3,123 , 2
1 , ,3,123 , 2
2 , aassdsds,33322,211 , 1
3 , ,21221,221 , 1

How I can accomplish to this problem?

I'm using postgresql 9.3.15.

thanks in advance.

Alessandro.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general