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
