Re: creating sequential timestamp

Hi all;

First of all, thanks to Joe Conway for this function;

I've loaded it in the server but when I try to use it exactly as in the
example Joe gives or in other way I obtain the answer:
---------------------
murciadb=# SELECT ts FROM generate_ts('14/10/1999 01:02:45 UTC','26/12/2000
06:01:00 UTC','3 hours') AS t(ts);
ERROR:  syntax error at or near "alias" at character 22
CONTEXT:  invalid type name "v_start alias for $1"
compile of PL/pgSQL function "generate_ts" near line 2
----------------------

Please, could some one tell me what am I doing wrong?

Thanks and best regards,

Javier
----------------------------------------------------------
El Lun 08 Mar 2004 22:47, Joe Conway escribió:
> javier garcia - CEBAS wrote:
> > Is it possible to directly create postgres tables with a timestamp column
> > with some specific interval and within a specific range.
> > For example every 5 minutes and between the beginning of 1999 and the end
> > of 2003?
>
> You could create a function to return that data, and fill a table with
> the output. Something like:
>
> create or replace function generate_ts(
>   timestamp with time zone,
>   timestamp with time zone,
>   interval
> )
> returns setof timestamp with time zone as '
> declare
>   v_start alias for $1;
>   v_end alias for $2;
>   v_interim alias for $3;
>   v_curr timestamp with time zone;
> begin
>   v_curr := v_start;
>   while v_curr <= v_end loop
>     return next v_curr;
>     v_curr := v_curr + v_interim;
>   end loop;
>   return;
> end;
> ' language plpgsql;
>
> regression=# select ts from generate_ts('today','tomorrow','3 hours') as
> t(ts);
>             ts
> ------------------------
>   2004-03-08 00:00:00-08
>   2004-03-08 03:00:00-08
>   2004-03-08 06:00:00-08
>   2004-03-08 09:00:00-08
>   2004-03-08 12:00:00-08
>   2004-03-08 15:00:00-08
>   2004-03-08 18:00:00-08
>   2004-03-08 21:00:00-08
>   2004-03-09 00:00:00-08
> (9 rows)
>
> HTH,
>
> Joe
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match