[SQL] Function with now() | time 'now' | etc...

[edipoelder]

Hi all, 

I wrote the folling function: 

DROP FUNCTION PROC_TESTE(INTEGER); 
CREATE FUNCTION PROC_TESTE(INTEGER) RETURNS INTEGER AS ' 
DECLARE 
SEQ RECORD; 
BEGIN 
SELECT NEXTVAL(''TEMPOS_ID_SEQ'') AS ID INTO SEQ; 
INSERT INTO TEMPOS (ID, INICIO) VALUES (SEQ.ID, NOW());  
   FOR I IN 1..$1 LOOP 
INSERT INTO TESTE(VALOR) VALUES (RANDOM()*$1); 
END LOOP; 
UPDATE TEMPOS SET FIM = NOW() WHERE ID = SEQ.ID; 
RETURN SEQ.ID; 
END;' 
LANGUAGE 'PLPGSQL'; 

And all times I run it ("select proc_teste(1);"), i got the folling 
behavour: 

teste=> SELECT * FROM TEMPOS; 
  inicio  |   fim| id 
--+--+ 
 15:12:17 | 15:12:17 | 23 
 15:12:18 | 15:12:18 | 24 
(...) 

It doesn't getting different times on each execution. I also tried put 
"timestamp 'now'" insted "now()". What am I doing wrong? 

Thank you, 

Edipo Elder 
[[EMAIL PROTECTED]] 

_
Oi! Você quer um iG-mail gratuito?
Então clique aqui: http://www.ig.com.br/paginas/assineigmail.html


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

[SQL] Help with 'now', now(), timestamp 'now', ...

[edipoelder]

Hi all, 
 

 
I´m developing some procedures in my db and i want know how much time my 

 
procedures take to execute. So, i write my first procedure (to test) as 
 
follows: 
 

 
CREATE FUNCTION TEST(INTEGER) RETURNS INTEGER AS ' 
 
DECLARE 
 
SEQ RECORD; 
 
BEGIN 
 
SELECT NEXTVAL(''TIMES_ID_SEQ'') AS ID INTO SEQ; 
 
INSERT INTO TIMES (ID, START) VALUES (SEQ.ID, NOW()); 
 
FOR I IN 1..$1 LOOP   ^ 
 
INSERT INTO TEST(ANUMBER) VALUES (RANDOM()*$1); 
 
END LOOP; 
 
UPDATE TIMES SET END = NOW() WHERE ID = SEQ.ID; 
 
RETURN SEQ.ID; ^ 
 
END;' 
 
LANGUAGE 'PLPGSQL'; 
 

 
and executed using "SELECT TEST(1);". When it finish, i do a "SELECT 

 
* FROM TIMES" and got: 
 

 
test=> SELECT * FROM TIMES; 
 
   start  |   end| id 
 
--+--+ 
 
 10:27:55 | 10:27:55 | 12 
 
 10:27:55 | 10:27:55 | 13 
 
 10:30:29 | 10:30:29 | 14 
 
 10:31:29 | 10:31:29 | 15 
 
(4 rows) 
 

 
In id = 12 and id = 13, i runned two times. Then I changed the function 

 
and run, at id = 14. Change again at id = 15. 
 

 
Where is underlined (), i tried to put, 'now', timestamp 'now', etc, 

 
and always get the same time. What i'm doing wrong? 
 

 
obs.:  -> TABLE TEST (ID SERIAL, ANUMBER INTEGER) 
 
   -> TABLE TIMES (ID SERIAL, START TIME, END TIME); 
 
   -> PostgreSQL 7.0.2 under Conectiva Linux
 

 

 
Thanks, 
 

 
Edipo Elder
 
[[EMAIL PROTECTED]] 

_
Oi! Você quer um iG-mail gratuito?
Então clique aqui: http://www.ig.com.br/paginas/assineigmail.html


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: Fwd: [SQL] Calling functions inside a function: behavior

[edipoelder]

Em 02 Apr 2001, Josh Berkus escreveu: 

>BEGIN WORK; 
>COMMIT WORK; 

In time... I think that could be great if postgresql implement a commit 
inside functions. 

Abracos, 

Edipo Elder 
[[EMAIL PROTECTED]] 

_
Oi! Você quer um iG-mail gratuito?
Então clique aqui: http://www.ig.com.br/paginas/assineigmail.html


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] Memory and performance

[edipoelder]

Hi all, 

I have noted that Postgresql don't make a good memory handle. I have 
made the tables/procedure (in attached file) and run it as "select bench(10, 
5000)". This will give a 5 records inserts (5 x 1). (well, I run it 
on a P200+64MB of RAM, under Linux, and Postgres 7.0.2. In a more powerfull 
machine, you can try other values). 

I get as result, the following times: 

 id | objname | benchtime 
+-+--- 
  1 | group 1 | 00:00:32 
  2 | group 2 | 00:00:47 
  3 | group 3 | 00:01:13 
  4 | group 4 | 00:01:41 
  5 | group 5 | 00:02:08 
(5 rows) 

Note that, with memory increse, the system becomes slow, even if the 
system has free memory to alocate (yes, 64MB is enough to this test). I 
didn't see the source code (yet), but I think that the data estructure used 
to keep the changed records is a kind of chained list; and to insert a new 
item, you have to walk to the end of this list. Can it be otimized? 

The system that I'm developing, I have about 25000 (persons) x 8 (exams) 
x 15 (answers per exam) = 300 records to process and it is VERY SLOW. 

thanks, 

Edipo Elder 
[[EMAIL PROTECTED]] 

_
Oi! Você quer um iG-mail gratuito?
Então clique aqui: http://www.ig.com.br/paginas/assineigmail.html

 teste.zip


---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl