Thread: Function with now() | time 'now' | etc...

Function with now() | time 'now' | etc...

From
edipoelder@ig.com.br
Date:
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);
ENDLOOP;        UPDATE TEMPOS SET FIM = NOW() WHERE ID = SEQ.ID;        RETURN SEQ.ID; 
 
END;' 
LANGUAGE 'PLPGSQL'; 
   And all times I run it ("select proc_teste(10000);"), 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    [edipoelder@ig.com.br] 

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



Re: Function with now() | time 'now' | etc...

From
Tom Lane
Date:
edipoelder@ig.com.br writes:
>     It doesn't getting different times on each execution. I also tried put 
> "timestamp 'now'" insted "now()". What am I doing wrong? 

now() is defined to return the time of the start of the current
transaction.  It won't change value inside a transaction.  See
http://www.postgresql.org/devel-corner/docs/postgres/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT
        regards, tom lane


Re: Function with now() | time 'now' | etc...

From
Cedar Cox
Date:
> edipoelder@ig.com.br writes:
> >     It doesn't getting different times on each execution. I also tried put 
> > "timestamp 'now'" insted "now()". What am I doing wrong? 
> 
> now() is defined to return the time of the start of the current
> transaction.  It won't change value inside a transaction.  See
> http://www.postgresql.org/devel-corner/docs/postgres/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

What about CURRENT_TIMESTAMP?  (This is standard SQL isn't it?)