Обсуждение: Re: For loop

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/

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: 1I 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

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.

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

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
>

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/

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.

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

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

Thank you all. The function from Jonah solves my problem.<br /><br /> Just a simple question:<br /><br /> One million
ofinserctions in <b>28,8</b> seconds with this kind of data (timestamp|character varring (50))  is a good performance
?<br/><br /> I let my questions to DBAs from PostgreSQL.<br /><br /> Sincerely<br /> Ezequias<br /> Adrian Klaver
escreveu:<blockquote cite="mid200703130644.20870.aklaver@comcast.net" type="cite"><pre wrap="">On Tuesday 13 March 2007
6:30am, Ezequias R. da Rocha wrote: </pre><blockquote type="cite"><pre wrap="">I quetion one more time. I must have a
function? Isn't another way to
 
implement it without using functions ?

Ezequias

Bart Degryse escreveu:   </pre><blockquote type="cite"><pre wrap="">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 <a class="moz-txt-link-abbreviated"
href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>
<a class="moz-txt-link-rfc2396E" href="mailto:pgsql-sql@postgresql.org"><mailto:pgsql-sql@postgresql.org></a>
     </pre><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><pre wrap="">"Ezequias R. da Rocha"
<aclass="moz-txt-link-rfc2396E" href="mailto:ezequias@fastcon.com.br"><ezequias@fastcon.com.br></a> 2007-03-13
14:19>>>           </pre></blockquote></blockquote></blockquote><pre wrap="">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
     </pre></blockquote></blockquote><pre wrap="">If you want to use plpgsql it needs to be within a function.
</pre></blockquote><br/> 

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

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.
>
>