pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFor loop
16+ messages / 6 participants
[nested] [flat]
* For loop
@ 2007-03-13 12:47 Ezequias R. da Rocha <ezequias@fastcon.com.br>
0 siblings, 2 replies; 16+ messages in thread
From: Ezequias R. da Rocha @ 2007-03-13 12:47 UTC (permalink / raw)
To: pgsql-sql
Hi list,
I would like to test the power of processing of postgresql (and my
server) by insert a large ammount of data on a table I have.
I would like to know how to implement a For... loop . With it I think I
could check the real power of my server.
Ezequias
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: For loop
@ 2007-03-13 13:00 Ezequias R. da Rocha <ezequias@fastcon.com.br>
parent: Ezequias R. da Rocha <ezequias@fastcon.com.br>
1 sibling, 0 replies; 16+ messages in thread
From: Ezequias R. da Rocha @ 2007-03-13 13:00 UTC (permalink / raw)
To: Ezequias R. da Rocha <ezequias@fastcon.com.br>; +Cc: pgsql-sql
Ezequias R. da Rocha escreveu:
> Hi list,
>
> I would like to test the power of processing of postgresql (and my
> server) by insert a large ammount of data on a table I have.
>
> I would like to know how to implement a For... loop . With it I think
> I could check the real power of my server.
>
> Ezequias
>
I tryed a way but it is not working. Please see it:
LOOP;
-- some computations
insert into carga (desc) values ('My Text');
IF count > 1000000 THEN
EXIT; -- exit loop
END IF;
END LOOP;
The postgresql reports the following error:
/
ERROR: syntax error at or near "LOOP"
SQL state: 42601
Character: 1/
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: For loop
@ 2007-03-13 13:13 Bart Degryse <Bart.Degryse@indicator.be>
parent: Ezequias R. da Rocha <ezequias@fastcon.com.br>
1 sibling, 2 replies; 16+ messages in thread
From: Bart Degryse @ 2007-03-13 13:13 UTC (permalink / raw)
To: pgsql-sql; +Cc: Ezequias R. da Rocha <ezequias@fastcon.com.br>
As you can see in the manual (http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html) you have to remove the ; after the initial LOOP
LOOP
statements
IF i > 1000000 THEN EXIT;
END LOOP;
Or you can try...
WHILE i <= 1000000 LOOP
statements
END LOOP;
Or even... (don't initialize i in this case)
FOR i IN 1 .. 1000000 LOOP
statements
END LOOP;
>>> "Ezequias R. da Rocha" <ezequias@fastcon.com.br> 2007-03-13 14:00 >>>
Ezequias R. da Rocha escreveu:
> Hi list,
>
> I would like to test the power of processing of postgresql (and my
> server) by insert a large ammount of data on a table I have.
>
> I would like to know how to implement a For... loop . With it I think
> I could check the real power of my server.
>
> Ezequias
>
I tryed a way but it is not working. Please see it:
LOOP;
-- some computations
insert into carga (desc) values ('My Text');
IF count > 1000000 THEN
EXIT; -- exit loop
END IF;
END LOOP;
The postgresql reports the following error:
/
ERROR: syntax error at or near "LOOP"
SQL state: 42601
Character: 1/
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: For loop
@ 2007-03-13 13:19 Ezequias R. da Rocha <ezequias@fastcon.com.br>
parent: Bart Degryse <Bart.Degryse@indicator.be>
1 sibling, 2 replies; 16+ messages in thread
From: Ezequias R. da Rocha @ 2007-03-13 13:19 UTC (permalink / raw)
To: Bart Degryse <Bart.Degryse@indicator.be>; +Cc: pgsql-sql
They are not working well.
Allways the same error.
(if while)
ERROR: syntax error at or near "WHILE"
SQL state: 42601
Character: 1
(if for)
ERROR: syntax error at or near "FOR"
SQL state: 42601
Character: 1
(if loop)
ERROR: syntax error at or near "LOOP"
SQL state: 42601
Character: 1
I installed the pgsql but I don't think it is a problem of language.
What could be wrong ? I am not using functions too. I am using the SQL
tool of PgAdmin III.
Any help would be glad.
Ezequias
Bart Degryse escreveu:
> As you can see in the manual
> (http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html)
> you have to remove the ; after the initial LOOP
>
> LOOP
> statements
> IF i > 1000000 THEN EXIT;
> END LOOP;
>
> Or you can try...
>
> WHILE i <= 1000000 LOOP
> statements
> END LOOP;
>
> Or even... (don't initialize i in this case)
>
> FOR i IN 1 .. 1000000 LOOP
> statements
> END LOOP;
>
> >>> "Ezequias R. da Rocha" <ezequias@fastcon.com.br> 2007-03-13 14:00 >>>
> Ezequias R. da Rocha escreveu:
> > Hi list,
> >
> > I would like to test the power of processing of postgresql (and my
> > server) by insert a large ammount of data on a table I have.
> >
> > I would like to know how to implement a For... loop . With it I think
> > I could check the real power of my server.
> >
> > Ezequias
> >
> I tryed a way but it is not working. Please see it:
>
> LOOP;
> -- some computations
> insert into carga (desc) values ('My Text');
> IF count > 1000000 THEN
> EXIT; -- exit loop
> END IF;
> END LOOP;
>
> The postgresql reports the following error:
> /
> ERROR: syntax error at or near "LOOP"
> SQL state: 42601
> Character: 1/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: For loop
@ 2007-03-13 13:27 Bart Degryse <Bart.Degryse@indicator.be>
parent: Bart Degryse <Bart.Degryse@indicator.be>
1 sibling, 1 reply; 16+ messages in thread
From: Bart Degryse @ 2007-03-13 13:27 UTC (permalink / raw)
To: pgsql-sql; +Cc: Ezequias R. da Rocha <ezequias@fastcon.com.br>
Please post your complete CREATE FUNCTION statement. Error code 42601 refers to some syntax error which probably occurs just before you start looping. To find it I need to see more code.
Please don't mail me directly. Only mail to pgsql-sql@postgresql.org
>>> "Ezequias R. da Rocha" <ezequias@fastcon.com.br> 2007-03-13 14:19 >>>
They are not working well.
Allways the same error.
(if while)
ERROR: syntax error at or near "WHILE"
SQL state: 42601
Character: 1
(if for)
ERROR: syntax error at or near "FOR"
SQL state: 42601
Character: 1
(if loop)
ERROR: syntax error at or near "LOOP"
SQL state: 42601
Character: 1
I installed the pgsql but I don't think it is a problem of language.
What could be wrong ? I am not using functions too. I am using the SQL
tool of PgAdmin III.
Any help would be glad.
Ezequias
Bart Degryse escreveu:
> As you can see in the manual
> (http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html)
> you have to remove the ; after the initial LOOP
>
> LOOP
> statements
> IF i > 1000000 THEN EXIT;
> END LOOP;
>
> Or you can try...
>
> WHILE i <= 1000000 LOOP
> statements
> END LOOP;
>
> Or even... (don't initialize i in this case)
>
> FOR i IN 1 .. 1000000 LOOP
> statements
> END LOOP;
>
> >>> "Ezequias R. da Rocha" <ezequias@fastcon.com.br> 2007-03-13 14:00 >>>
> Ezequias R. da Rocha escreveu:
> > Hi list,
> >
> > I would like to test the power of processing of postgresql (and my
> > server) by insert a large ammount of data on a table I have.
> >
> > I would like to know how to implement a For... loop . With it I think
> > I could check the real power of my server.
> >
> > Ezequias
> >
> I tryed a way but it is not working. Please see it:
>
> LOOP;
> -- some computations
> insert into carga (desc) values ('My Text');
> IF count > 1000000 THEN
> EXIT; -- exit loop
> END IF;
> END LOOP;
>
> The postgresql reports the following error:
> /
> ERROR: syntax error at or near "LOOP"
> SQL state: 42601
> Character: 1/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: For loop
@ 2007-03-13 13:28 Alvaro Herrera <alvherre@commandprompt.com>
parent: Ezequias R. da Rocha <ezequias@fastcon.com.br>
1 sibling, 0 replies; 16+ messages in thread
From: Alvaro Herrera @ 2007-03-13 13:28 UTC (permalink / raw)
To: Ezequias R. da Rocha <ezequias@fastcon.com.br>; +Cc: Bart Degryse <Bart.Degryse@indicator.be>; pgsql-sql
Ezequias R. da Rocha wrote:
> They are not working well.
>
> Allways the same error.
>
> (if while)
> ERROR: syntax error at or near "WHILE"
> SQL state: 42601
> Character: 1
You may have a problem in the code just _before_ these lines.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: For loop
@ 2007-03-13 13:30 Ezequias R. da Rocha <ezequias@fastcon.com.br>
parent: Bart Degryse <Bart.Degryse@indicator.be>
0 siblings, 5 replies; 16+ messages in thread
From: Ezequias R. da Rocha @ 2007-03-13 13:30 UTC (permalink / raw)
To: pgsql-sql
I quetion one more time. I must have a function ? Isn't another way to
implement it without using functions ?
Ezequias
Bart Degryse escreveu:
> Please post your complete CREATE FUNCTION statement. Error code 42601
> refers to some syntax error which probably occurs just before you
> start looping. To find it I need to see more code.
> Please don't mail me directly. Only mail to pgsql-sql@postgresql.org
> <mailto:pgsql-sql@postgresql.org>
>
> >>> "Ezequias R. da Rocha" <ezequias@fastcon.com.br> 2007-03-13 14:19 >>>
> They are not working well.
>
> Allways the same error.
>
> (if while)
> ERROR: syntax error at or near "WHILE"
> SQL state: 42601
> Character: 1
>
> (if for)
>
> ERROR: syntax error at or near "FOR"
> SQL state: 42601
> Character: 1
>
> (if loop)
> ERROR: syntax error at or near "LOOP"
> SQL state: 42601
> Character: 1
> I installed the pgsql but I don't think it is a problem of language.
> What could be wrong ? I am not using functions too. I am using the SQL
> tool of PgAdmin III.
>
> Any help would be glad.
>
> Ezequias
>
> Bart Degryse escreveu:
> > As you can see in the manual
> >
> (http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html)
>
> > you have to remove the ; after the initial LOOP
> >
> > LOOP
> > statements
> > IF i > 1000000 THEN EXIT;
> > END LOOP;
> >
> > Or you can try...
> >
> > WHILE i <= 1000000 LOOP
> > statements
> > END LOOP;
> >
> > Or even... (don't initialize i in this case)
> >
> > FOR i IN 1 .. 1000000 LOOP
> > statements
> > END LOOP;
> >
> > >>> "Ezequias R. da Rocha" <ezequias@fastcon.com.br> 2007-03-13
> 14:00 >>>
> > Ezequias R. da Rocha escreveu:
> > > Hi list,
> > >
> > > I would like to test the power of processing of postgresql (and my
> > > server) by insert a large ammount of data on a table I have.
> > >
> > > I would like to know how to implement a For... loop . With it I think
> > > I could check the real power of my server.
> > >
> > > Ezequias
> > >
> > I tryed a way but it is not working. Please see it:
> >
> > LOOP;
> > -- some computations
> > insert into carga (desc) values ('My Text');
> > IF count > 1000000 THEN
> > EXIT; -- exit loop
> > END IF;
> > END LOOP;
> >
> > The postgresql reports the following error:
> > /
> > ERROR: syntax error at or near "LOOP"
> > SQL state: 42601
> > Character: 1/
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/docs/faq
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: For loop
@ 2007-03-13 13:32 Adrian Klaver <aklaver@comcast.net>
parent: Ezequias R. da Rocha <ezequias@fastcon.com.br>
1 sibling, 0 replies; 16+ messages in thread
From: Adrian Klaver @ 2007-03-13 13:32 UTC (permalink / raw)
To: pgsql-sql
On Tuesday 13 March 2007 6:19 am, Ezequias R. da Rocha wrote:
> They are not working well.
>
> Allways the same error.
>
> (if while)
> ERROR: syntax error at or near "WHILE"
> SQL state: 42601
> Character: 1
>
> (if for)
>
> ERROR: syntax error at or near "FOR"
> SQL state: 42601
> Character: 1
>
> (if loop)
> ERROR: syntax error at or near "LOOP"
> SQL state: 42601
> Character: 1
> I installed the pgsql but I don't think it is a problem of language.
> What could be wrong ? I am not using functions too. I am using the SQL
> tool of PgAdmin III.
>
> Any help would be glad.
>
Can we see the complete function?
--
Adrian Klaver
aklaver@comcast.net
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: For loop
@ 2007-03-13 13:39 Jonah H. Harris <jonah.harris@gmail.com>
parent: Ezequias R. da Rocha <ezequias@fastcon.com.br>
4 siblings, 0 replies; 16+ messages in thread
From: Jonah H. Harris @ 2007-03-13 13:39 UTC (permalink / raw)
To: Ezequias R. da Rocha <ezequias@fastcon.com.br>; +Cc: pgsql-sql
On 3/13/07, Ezequias R. da Rocha <ezequias@fastcon.com.br> wrote:
> I quetion one more time. I must have a function ? Isn't another way to
> implement it without using functions ?
Not in PostgreSQL. Here's a sample of something similar to what you were doing.
CREATE LANGUAGE plpgsql;
CREATE TABLE carga (
id NUMERIC,
desc_txt TEXT,
PRIMARY KEY (id));
CREATE OR REPLACE FUNCTION for_loop_func (num_iter INTEGER)
RETURNS void AS
$$
DECLARE
iter NUMERIC;
tmp_num NUMERIC;
BEGIN
FOR iter IN 1 .. num_iter
LOOP
-- some computations
tmp_num = iter * 2;
INSERT INTO carga
(id, desc_txt)
VALUES (tmp_num, 'My Text for ' || iter || '*2 = ' || tmp_num);
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
--SELECT for_loop_func(1000000);
SELECT for_loop_func(10);
--
Jonah H. Harris, Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
33 Wood Ave S, 3rd Floor | jharris@enterprisedb.com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: For loop
@ 2007-03-13 13:40 Alvaro Herrera <alvherre@commandprompt.com>
parent: Ezequias R. da Rocha <ezequias@fastcon.com.br>
4 siblings, 0 replies; 16+ messages in thread
From: Alvaro Herrera @ 2007-03-13 13:40 UTC (permalink / raw)
To: Ezequias R. da Rocha <ezequias@fastcon.com.br>; +Cc: pgsql-sql
Ezequias R. da Rocha wrote:
> I quetion one more time. I must have a function ? Isn't another way to
> implement it without using functions ?
The syntax you show is plpgsql. You cannot use that without a function.
(The alternative would be to script the bulk insertion client-side,
which I'd think is smarter anyway).
If what you need to do is batch processing of rows to produce other
rows, then maybe you can craft an INSERT .. SELECT query to do it
instead of this row-at-a-time approach.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: For loop
@ 2007-03-13 13:41 Bart Degryse <Bart.Degryse@indicator.be>
parent: Ezequias R. da Rocha <ezequias@fastcon.com.br>
4 siblings, 0 replies; 16+ messages in thread
From: Bart Degryse @ 2007-03-13 13:41 UTC (permalink / raw)
To: pgsql-sql; +Cc: Ezequias R. da Rocha <ezequias@fastcon.com.br>
AFAK you can also use them in eg CREATE TRIGGER and on the psql command prompt.
What are you trying to accomplish. When we understand that, maybe we can point you in the right direction.
>>> "Ezequias R. da Rocha" <ezequias@fastcon.com.br> 2007-03-13 14:30 >>>
I quetion one more time. I must have a function ? Isn't another way to
implement it without using functions ?
Ezequias
Bart Degryse escreveu:
> Please post your complete CREATE FUNCTION statement. Error code 42601
> refers to some syntax error which probably occurs just before you
> start looping. To find it I need to see more code.
> Please don't mail me directly. Only mail to pgsql-sql@postgresql.org
> <mailto:pgsql-sql@postgresql.org>
>
> >>> "Ezequias R. da Rocha" <ezequias@fastcon.com.br> 2007-03-13 14:19 >>>
> They are not working well.
>
> Allways the same error.
>
> (if while)
> ERROR: syntax error at or near "WHILE"
> SQL state: 42601
> Character: 1
>
> (if for)
>
> ERROR: syntax error at or near "FOR"
> SQL state: 42601
> Character: 1
>
> (if loop)
> ERROR: syntax error at or near "LOOP"
> SQL state: 42601
> Character: 1
> I installed the pgsql but I don't think it is a problem of language.
> What could be wrong ? I am not using functions too. I am using the SQL
> tool of PgAdmin III.
>
> Any help would be glad.
>
> Ezequias
>
> Bart Degryse escreveu:
> > As you can see in the manual
> >
> (http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html)
>
> > you have to remove the ; after the initial LOOP
> >
> > LOOP
> > statements
> > IF i > 1000000 THEN EXIT;
> > END LOOP;
> >
> > Or you can try...
> >
> > WHILE i <= 1000000 LOOP
> > statements
> > END LOOP;
> >
> > Or even... (don't initialize i in this case)
> >
> > FOR i IN 1 .. 1000000 LOOP
> > statements
> > END LOOP;
> >
> > >>> "Ezequias R. da Rocha" <ezequias@fastcon.com.br> 2007-03-13
> 14:00 >>>
> > Ezequias R. da Rocha escreveu:
> > > Hi list,
> > >
> > > I would like to test the power of processing of postgresql (and my
> > > server) by insert a large ammount of data on a table I have.
> > >
> > > I would like to know how to implement a For... loop . With it I think
> > > I could check the real power of my server.
> > >
> > > Ezequias
> > >
> > I tryed a way but it is not working. Please see it:
> >
> > LOOP;
> > -- some computations
> > insert into carga (desc) values ('My Text');
> > IF count > 1000000 THEN
> > EXIT; -- exit loop
> > END IF;
> > END LOOP;
> >
> > The postgresql reports the following error:
> > /
> > ERROR: syntax error at or near "LOOP"
> > SQL state: 42601
> > Character: 1/
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/docs/faq
>
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: For loop
@ 2007-03-13 13:42 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Ezequias R. da Rocha <ezequias@fastcon.com.br>
4 siblings, 0 replies; 16+ messages in thread
From: A. Kretschmer @ 2007-03-13 13:42 UTC (permalink / raw)
To: pgsql-sql
am Tue, dem 13.03.2007, um 10:30:59 -0300 mailte Ezequias R. da Rocha folgendes:
> I quetion one more time. I must have a function ? Isn't another way to
> implement it without using functions ?
Yes, you need a function. SQL doesn't know LOOPs.
And, please, no silly fullquote.
[100 lines dropped]
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: For loop
@ 2007-03-13 13:44 Adrian Klaver <aklaver@comcast.net>
parent: Ezequias R. da Rocha <ezequias@fastcon.com.br>
4 siblings, 1 reply; 16+ messages in thread
From: Adrian Klaver @ 2007-03-13 13:44 UTC (permalink / raw)
To: pgsql-sql
On Tuesday 13 March 2007 6:30 am, Ezequias R. da Rocha wrote:
> I quetion one more time. I must have a function ? Isn't another way to
> implement it without using functions ?
>
> Ezequias
>
> Bart Degryse escreveu:
> > Please post your complete CREATE FUNCTION statement. Error code 42601
> > refers to some syntax error which probably occurs just before you
> > start looping. To find it I need to see more code.
> > Please don't mail me directly. Only mail to pgsql-sql@postgresql.org
> > <mailto:pgsql-sql@postgresql.org>
> >
> > >>> "Ezequias R. da Rocha" <ezequias@fastcon.com.br> 2007-03-13 14:19 >>>
> >
> > They are not working well.
> >
> > Allways the same error.
> >
> > (if while)
> > ERROR: syntax error at or near "WHILE"
> > SQL state: 42601
> > Character: 1
> >
> > (if for)
> >
> > ERROR: syntax error at or near "FOR"
> > SQL state: 42601
> > Character: 1
> >
> > (if loop)
> > ERROR: syntax error at or near "LOOP"
> > SQL state: 42601
> > Character: 1
> > I installed the pgsql but I don't think it is a problem of language.
> > What could be wrong ? I am not using functions too. I am using the SQL
> > tool of PgAdmin III.
> >
> > Any help would be glad.
> >
> > Ezequias
> >
If you want to use plpgsql it needs to be within a function.
--
Adrian Klaver
aklaver@comcast.net
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: For loop
@ 2007-03-13 14:03 Ezequias R. da Rocha <ezequias@fastcon.com.br>
parent: Adrian Klaver <aklaver@comcast.net>
0 siblings, 1 reply; 16+ messages in thread
From: Ezequias R. da Rocha @ 2007-03-13 14:03 UTC (permalink / raw)
To: pgsql-sql
Thank you all. The function from Jonah solves my problem.
Just a simple question:
One million of inserctions in *28,8* seconds with this kind of data
(timestamp|character varring (50)) is a good performance ?
I let my questions to DBAs from PostgreSQL.
Sincerely
Ezequias
Adrian Klaver escreveu:
> On Tuesday 13 March 2007 6:30 am, Ezequias R. da Rocha wrote:
>
>> I quetion one more time. I must have a function ? Isn't another way to
>> implement it without using functions ?
>>
>> Ezequias
>>
>> Bart Degryse escreveu:
>>
>>> Please post your complete CREATE FUNCTION statement. Error code 42601
>>> refers to some syntax error which probably occurs just before you
>>> start looping. To find it I need to see more code.
>>> Please don't mail me directly. Only mail to pgsql-sql@postgresql.org
>>> <mailto:pgsql-sql@postgresql.org>
>>>
>>>
>>>>>> "Ezequias R. da Rocha" <ezequias@fastcon.com.br> 2007-03-13 14:19 >>>
>>>>>>
>>> They are not working well.
>>>
>>> Allways the same error.
>>>
>>> (if while)
>>> ERROR: syntax error at or near "WHILE"
>>> SQL state: 42601
>>> Character: 1
>>>
>>> (if for)
>>>
>>> ERROR: syntax error at or near "FOR"
>>> SQL state: 42601
>>> Character: 1
>>>
>>> (if loop)
>>> ERROR: syntax error at or near "LOOP"
>>> SQL state: 42601
>>> Character: 1
>>> I installed the pgsql but I don't think it is a problem of language.
>>> What could be wrong ? I am not using functions too. I am using the SQL
>>> tool of PgAdmin III.
>>>
>>> Any help would be glad.
>>>
>>> Ezequias
>>>
>>>
> If you want to use plpgsql it needs to be within a function.
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: For loop
@ 2007-03-13 14:27 Alvaro Herrera <alvherre@commandprompt.com>
parent: Ezequias R. da Rocha <ezequias@fastcon.com.br>
0 siblings, 1 reply; 16+ messages in thread
From: Alvaro Herrera @ 2007-03-13 14:27 UTC (permalink / raw)
To: Ezequias R. da Rocha <ezequias@fastcon.com.br>; +Cc: pgsql-sql
Ezequias R. da Rocha wrote:
> Thank you all. The function from Jonah solves my problem.
>
> Just a simple question:
>
> One million of inserctions in *28,8* seconds with this kind of data
> (timestamp|character varring (50)) is a good performance ?
>
> I let my questions to DBAs from PostgreSQL.
I don't know, but Jonah's function can be better written using pure SQL,
which should be faster. Something like this:
create table whatever (a text, b int);
insert into whatever
select 'My text from iteration ' || a, a * 2
from generate_series(1, 1000000) a;
I don't claim this works as is, but you should get the hang of it.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: For loop
@ 2007-03-13 14:32 Ezequias R. da Rocha <ezequias@fastcon.com.br>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 0 replies; 16+ messages in thread
From: Ezequias R. da Rocha @ 2007-03-13 14:32 UTC (permalink / raw)
To: Ezequias R. da Rocha <ezequias@fastcon.com.br>; pgsql-sql
I rewritte the Jonah's function to:
FOR iter IN 1 .. num_iter
LOOP
-- some computations
INSERT INTO carga (descricao) VALUES ('CardPass');
END LOOP;
My ID is now() by default
And it was in 23sec
After I changed the insert to:
INSERT INTO carga (id,descricao) VALUES ('2007-03-13
10:20:20','CardPass');
And it was in:
19485ms
Great don't you ?
Ezequias
Alvaro Herrera escreveu:
> Ezequias R. da Rocha wrote:
>
>> Thank you all. The function from Jonah solves my problem.
>>
>> Just a simple question:
>>
>> One million of inserctions in *28,8* seconds with this kind of data
>> (timestamp|character varring (50)) is a good performance ?
>>
>> I let my questions to DBAs from PostgreSQL.
>>
>
> I don't know, but Jonah's function can be better written using pure SQL,
> which should be faster. Something like this:
>
> create table whatever (a text, b int);
> insert into whatever
> select 'My text from iteration ' || a, a * 2
> from generate_series(1, 1000000) a;
>
> I don't claim this works as is, but you should get the hang of it.
>
>
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2007-03-13 14:32 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-03-13 12:47 For loop Ezequias R. da Rocha <ezequias@fastcon.com.br>
2007-03-13 13:00 ` Ezequias R. da Rocha <ezequias@fastcon.com.br>
2007-03-13 13:13 ` Bart Degryse <Bart.Degryse@indicator.be>
2007-03-13 13:19 ` Ezequias R. da Rocha <ezequias@fastcon.com.br>
2007-03-13 13:28 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-03-13 13:32 ` Adrian Klaver <aklaver@comcast.net>
2007-03-13 13:27 ` Bart Degryse <Bart.Degryse@indicator.be>
2007-03-13 13:30 ` Ezequias R. da Rocha <ezequias@fastcon.com.br>
2007-03-13 13:39 ` Jonah H. Harris <jonah.harris@gmail.com>
2007-03-13 13:40 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-03-13 13:41 ` Bart Degryse <Bart.Degryse@indicator.be>
2007-03-13 13:42 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-03-13 13:44 ` Adrian Klaver <aklaver@comcast.net>
2007-03-13 14:03 ` Ezequias R. da Rocha <ezequias@fastcon.com.br>
2007-03-13 14:27 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-03-13 14:32 ` Ezequias R. da Rocha <ezequias@fastcon.com.br>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox