Re: plpgsql - TIMESTAMP variables in EXTRACT

On Wed, 15 Jun 2005, Matthew Phillips wrote:

> Hi all,
>
> I have the following in a plpgsql proc on 7.3.4:
>
> <code>
> DECLARE
> ...
> curTime TIMESTAMP;
> ppsCnt  INT;
>
> BEGIN
> ...
>
> -- this works
> SELECT INTO curTime localtimestamp;
>
> -- get unix seconds from current time (doesn't work)
> SELECT INTO ppsCnt EXTRACT (EPOCH FROM TIMESTAMP curTime );
> --               parser complains here ^

I think you want EXTRACT (EPOCH FROM curTime).  I don't have 7.3 around,
but in 7.4 at least I was able to do something like the following:

create or replace function ff() returns int as '
declare
 f timestamp(0); -- if you don''t want fractional seconds
 a int;
begin
 select into f localtimestamp;
 select into a extract(epoch from f);
 return a;
end;' language 'plpgsql';

TIMESTAMP <blah> is the syntax for a timestamp literal.