Re: dynamic interval in plpgsql

On Mon, Mar 29, 2004 at 12:51:11PM +0200, Thilo Hille wrote:
> hi,
> i work on a stored procedure which does some timespecific calculations
> in plpgsql.
> in a loop i want to increase a timestamp by a changing interval. but i
> found no way to assign a variable to INTERVAL .
> finally i used plpython for the function but i still wonder if it could
> be done with plpgsql?
>
> regards thilo

You can do that with some select statements, non?
run the following and hope it helps
------------------------------------
create sequence test_interval_id_seq;
create table test_interval (
id integer UNIQUE DEFAULT nextval('test_interval_id_seq'),
formulation varchar(100),
interval_col interval,
check (interval_col >= '0 day'::interval)
);
insert into test_interval (formulation,interval_col) values ('1 day','1 day');
insert into test_interval (formulation, interval_col) values ('timestamp ''today'' - timestamp ''tomorrow''',timestamp 'today'- timestamp 'tomorrow');
insert into test_interval (formulation, interval_col) values (
'timestamp ''today''- timestamp ''yesterday''',
timestamp 'today'- timestamp 'yesterday');
insert into test_interval (formulation, interval_col) values (
'timestamp ''tomorrow''- timestamp ''yesterday''',
timestamp 'tomorrow'- timestamp 'yesterday');
insert into test_interval (formulation, interval_col) values (
'now() - timestamp ''yesterday''',
now() - timestamp 'yesterday');
insert into test_interval (formulation, interval_col) values (
'timestamp ''today'' + interval ''1 month 04:01''',
timestamp 'today' + interval '1 month 04:01');
select * from test_interval;
drop table test_interval;
drop sequence test_interval_id_seq;

--
joe speigle
www.sirfsup.com