Skip site navigation (1) Skip section navigation (2)

Re: pl/pgsql and returns timestamp type

From: Terry Yapt <yapt(at)technovell(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: josh(at)agliodbs(dot)com, pgsql-novice(at)postgresql(dot)org
Subject: Re: pl/pgsql and returns timestamp type
Date: 2002-09-10 22:38:50
Message-ID: 3D7E747A.ED4D9820@technovell.com (view raw or flat)
Thread:
Lists: pgsql-novice
Hi Tom...

Here is the complete function modified and running fine now with its result on my test computer.

Thanks Tom, Josh and best regards.....

--====================================
DROP TABLE test;
CREATE TABLE test
(
  clave numeric(7,0) not null,
  PRIMARY KEY (clave)
) WITHOUT OIDS;
--====================================
DROP FUNCTION f_test(numeric, numeric);
CREATE OR REPLACE FUNCTION f_test(numeric, numeric) RETURNS interval AS '
  DECLARE
    p_datod ALIAS FOR $1;
    p_datoh ALIAS FOR $2;
    --
    tdesde timestamp;
    thasta timestamp;
  BEGIN
    select timeofday() into tdesde;
    --
    FOR X IN p_datod..p_datoh LOOP
      INSERT INTO test VALUES (x);    
    END LOOP;
    --
    select timeofday() into thasta;
    --
    RETURN thasta - tdesde;
  END;
' LANGUAGE 'plpgsql';
--====================================
select f_test(1,9999);

/*
     f_test      
-----------------
 00:00:02.467566
(1 row)
*/

Tom Lane wrote:
> 
> Josh Berkus <josh(at)agliodbs(dot)com> writes:
> >> But then... How could I obtain "variable global values" (like the timestamp
> >> for example) in different function locations whit its "at time" values ???
> 
> > Not that I know of.   I'd suggest, instead, that you call the second now()
> > statement after the function completes.
> 
> There is a function that gives true realtime, rather than transaction
> start time as now() does.  I think it's called timeofday(), but check
> the manual.
> 
>                         regards, tom lane

In response to

pgsql-novice by date

Next:From: Brendon MatthewsDate: 2002-09-11 00:39:17
Subject: HELP!!!
Previous:From: Roland RobertsDate: 2002-09-10 20:40:44
Subject: Unique indices and nulls

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group