Обсуждение: plpgsql - TIMESTAMP variables in EXTRACT

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 ^

-- get unix seconds from random string (works)
SELECT INTO ppsCnt EXTRACT (EPOCH FROM TIMESTAMP
     \'1990-05-04 01:02:03\' );

-- do stuff with ppsCnt and curTime
...

</code>

Here are the error messages:
WARNING:  Error occurred while executing PL/pgSQL function XXX
WARNING:  line XXX at select into variables
ERROR:  parser: parse error at or near "$1" at character XX

All I would like to do is get the current time and convert
it to an integer value. Am I attempting to do the impossible or
is this another simpler way to shove unix seconds of the current
time in a variable? Please also inform me as to why my current
syntax is invalid.

Thanks,
Matthew

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.

Stephan,
Thanks, this does work. I assume that the usage of 'TIMESTAMP'
only applies when a literal representation of the date is
given.

Matthew

Stephan Szabo wrote:
> 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.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>