Thread: New feature proposal

Dear Developers,

I would like to suggest the inclusion of an extension
in PostgreSQL. There are instances, I found, when one
needs to INSERT several times the same record in a
table. The front-end application can do it easy in a
loop of a sort, but on remote servers (and that's the
norm these days) it creates unnecessary network
traffic.

My suggestion is to allow INSERT to do it REPEAT x.
This should allow, in my view, the followings:
a) INSERT INTO my_table (field1, field2, field3)  VALUES (value1, value2, value3) REPEAT 5;
should insert 5 identical rows
b) INSERT INTO my_table (field1, field2, field3)  VALUES (x, value2/x, value3) REPEAT (x=3);
should insert the followings:
1, value2, value3
2, value2/2, value3
3, value2/3, value3

In other words, this form of INSERT shold instruct the
SQL engine to perform a for loop. Maybe instead of
REPEAT there should be another word, maybe the for
loop should allow going with a different step than
the default one and go backward as well (easy to do
with a negative step).

This suggestion comes for a practical project that I
have.
I haven't feel the need for something like that in an
UPDATE, but I can imagine that it may happen:

UPDATE my_table SET field1=value1*x WHERE condition
REPEAT (x=(SELECT repetition FROM table2 WHERE
condition2) STEP -1.5);

Best regards,
Sorin Schwimmer


____________________________________________________________________________________
Expecting? Get great news right away with email Auto-Check. 
Try the Yahoo! Mail Beta.
http://advision.webevents.yahoo.com/mailbeta/newmail_tools.html 

On Wed, 2007-01-24 at 08:26 -0800, Sorin Schwimmer wrote:
> The front-end application can do it easy in a
> loop of a sort, but on remote servers (and that's the
> norm these days) it creates unnecessary network
> traffic.

You can avoid this easily via a stored procedure.

> My suggestion is to allow INSERT to do it REPEAT x.

We generally try to avoid non-standard extensions to SQL to accomplish
things that can be comfortably expressed in standard SQL, or via the
existing Postgres constructs (e.g. PL/PgSQL stored procedures).

-Neil

On Wed, 24 Jan 2007, Sorin Schwimmer wrote:

> Dear Developers,
>
> I would like to suggest the inclusion of an extension
> in PostgreSQL. There are instances, I found, when one
> needs to INSERT several times the same record in a
> table. The front-end application can do it easy in a
> loop of a sort, but on remote servers (and that's the
> norm these days) it creates unnecessary network
> traffic.
>
> My suggestion is to allow INSERT to do it REPEAT x.
> This should allow, in my view, the followings:
> a) INSERT INTO my_table (field1, field2, field3)
>    VALUES (value1, value2, value3) REPEAT 5;

postgres=# create table baz (i int, j text);
CREATE TABLE
postgres=# insert into baz (i, j) select 1, 'hello' from
generate_series(1, 5);
INSERT 0 5
postgres=# select * from baz;i |   j
---+-------1 | hello1 | hello1 | hello1 | hello1 | hello
(5 rows)

> b) INSERT INTO my_table (field1, field2, field3)
>    VALUES (x, value2/x, value3) REPEAT (x=3);
> should insert the followings:
> 1, value2, value3
> 2, value2/2, value3
> 3, value2/3, value3

Yuk! Besides, it can be done similarly to the above.

> This suggestion comes for a practical project that I
> have.

Well, the good thing is, you can use generate_series() now! :-)

Thanks,

Gavin

Sorin Schwimmer <sxn02@yahoo.com> writes:
> My suggestion is to allow INSERT to do it REPEAT x.

You can do that today.

INSERT INTO foo SELECT const1,const2,... FROM generate_series(1,1000);
        regards, tom lane

Dear Developers,

Thanks for your answers. I didn't know about
generate_series, but it looks to be exactly what
I was suggesting.

Regards,
Sorin Schwimmer


____________________________________________________________________________________
Do you Yahoo!?
Everyone is raving about the all-new Yahoo! Mail beta.
http://new.mail.yahoo.com