Postgres Pro
Downloads
Home   >   mailing lists

Thread: plpgsql extract epoch problem

plpgsql extract epoch problem

From
Darren Ferguson
Date:
Hey all

I am trying to extract the epoch from a timestamp variable in plpgsql.
The function is as follows:

create or replace function test() returns integer as '
declare
  _test timestamp with time zone;
  _f integer;
begin
  _test := current_timestamp;
  _f := extract(epoch from timestamp _test);
  return _f;
end;' language 'plpgsql';

Any ideas why this will not work????

The error is as follows:
pts=> select test();
WARNING:  Error occurred while executing PL/pgSQL function test
WARNING:  line 6 at assignment
ERROR:  parser: parse error at or near "$1" at character 40

And that is the _test variable

Any help greatly apprechiated
Darren

Re: plpgsql extract epoch problem

From
Darren Ferguson
Date:
Sorry found the answer
I was taking the docs to literal

It should be extract(epoch from _test)

Sorry for the email
Darren

Darren Ferguson wrote:

> Hey all
>
> I am trying to extract the epoch from a timestamp variable in plpgsql.
> The function is as follows:
>
> create or replace function test() returns integer as '
> declare
>  _test timestamp with time zone;
>  _f integer;
> begin
>  _test := current_timestamp;
>  _f := extract(epoch from timestamp _test);
>  return _f;
> end;' language 'plpgsql';
>
> Any ideas why this will not work????
>
> The error is as follows:
> pts=> select test();
> WARNING:  Error occurred while executing PL/pgSQL function test
> WARNING:  line 6 at assignment
> ERROR:  parser: parse error at or near "$1" at character 40
>
> And that is the _test variable
>
> Any help greatly apprechiated
> Darren
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>      subscribe-nomail command to majordomo@postgresql.org so that your
>      message can get through to the mailing list cleanly

Re: plpgsql extract epoch problem

From
Alvaro Herrera
Date:
On Wed, Oct 22, 2003 at 12:50:22PM -0400, Darren Ferguson wrote:

> create or replace function test() returns integer as '
> declare
>  _test timestamp with time zone;
>  _f integer;
> begin
>  _test := current_timestamp;
>  _f := extract(epoch from timestamp _test);

Why not simply
_f := extract(epoch from _test);
?

>  return _f;
> end;' language 'plpgsql';

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Hay que recordar que la existencia en el cosmos, y particularmente la
elaboración de civilizaciones dentre de él no son, por desgracia,
nada idílicas" (Ijon Tichy)