Skip site navigation (1) Skip section navigation (2)

Re: Return count between timestamps

From: Andreas Kretschmer <akretschmer(at)spamfence(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Return count between timestamps
Date: 2006-03-19 11:51:13
Message-ID: 20060319115113.GB6441@KanotixBox (view raw or flat)
Thread:
Lists: pgsql-novice
Andreas Kretschmer <akretschmer(at)spamfence(dot)net> schrieb:

> Jonathan Davies <jonathan(at)nixondesign(dot)com> schrieb:
> 
> > 
> > Hi
> > I have a car parking reservation table that stores 2 timestamps entry_date and
> > exit_date.
> > There is a maximum number of car parking places, and I want to check that on
> > each day between the 2 requested reservation dates, the count of the existing
> > records does not exceed the maximum.

Btw.:

you can create a trigger like this:
(with a fixed limit of 6 places)


create or replace function check_parking() returns trigger as $$
declare counter int;
begin
        select into counter max(x.sum) from (select (NEW.entry + (g.n||'days')::interval)::date,
                sum(case when (NEW.entry + (g.n||'days')::interval)::date between a.entry and a.exit then 1 else 0 end)
        from parking a, generate_series(0,NEW.exit-NEW.entry) g(n) group by g.n order by 1) as x;
        if counter > 5 then -- limit -1!!!
                raise exception 'not possible';
        else
                return NEW;
        end if;
end;
$$ language plpgsql;

create trigger parking_check before insert or update on parking for each row execute procedure check_parking();

Now a test:

test=# select * from parking ;
 id |   entry    |    exit
----+------------+------------
  1 | 2006-03-01 | 2006-03-30
  2 | 2006-03-15 | 2006-04-15
  3 | 2006-03-30 | 2006-04-30
(3 rows)

test=# insert into parking values (4,'2006/03/02','2006/03/20');
INSERT 0 1
test=# insert into parking values (5,'2006/03/12','2006/03/20');
INSERT 0 1
test=# insert into parking values (5,'2006/03/10','2006/03/20');
INSERT 0 1
test=# insert into parking values (6,'2006/03/10','2006/03/20');
INSERT 0 1
test=# insert into parking values (7,'2006/03/10','2006/03/20');
ERROR:  not possible


HTH, Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

In response to

pgsql-novice by date

Next:From: Luis SilvaDate: 2006-03-20 10:23:51
Subject: Trigger problem
Previous:From: Andreas KretschmerDate: 2006-03-19 09:41:52
Subject: Re: Return count between timestamps

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group