create or replace function simple_counter()  
returns integer as                          
'                                          
	try:                                   
		SD["currval"] += 1                 
	except:                              
		SD["currval"] = 1                
	return SD["currval"]               
' language 'plpython';               

-- SCOPE is Connection
create or replace function simple_counter(integer)
returns integer as
'
	if args[0] == 1:
		SD["currval"] = 0
		return SD["currval"]
	else:
		try:
			SD["currval"] += 1
		except:
			SD["currval"] = 0
	return SD["currval"]
' language 'plpython';


create table buslog (
	bus	integer,	-- references buses
	route	text,		-- references routes
	sord	integer,	-- references stops
	stop	text,		-- references stops
	atime	timestamp
);

insert into buslog values (
1, 'BART','0','Yard','12-May-2003 10:47:54');
insert into buslog values (
3, 'BART','0','Yard','12-May-2003 10:59:16');
insert into buslog values (
5, 'BART','0','Yard','12-May-2003 11:08:36');
insert into buslog values (
7, 'BART','0','Yard','12-May-2003 11:22:19');
insert into buslog values (
1, 'BART','1','No.Berk','12-May-2003 10:47:54');
insert into buslog values (
3, 'BART','1','No.Berk','12-May-2003 10:59:16');
insert into buslog values (
5, 'BART','1','No.Berk','12-May-2003 11:08:36');
insert into buslog values (
7, 'BART','1','No.Berk','12-May-2003 11:22:19');
insert into buslog values (
1, 'BART','2','Ashby','12-May-2003 10:59:13');
insert into buslog values (
3, 'BART','2','Ashby','12-May-2003 11:14:44');
insert into buslog values (
5, 'BART','2','Ashby','12-May-2003 11:18:22');
insert into buslog values (
7, 'BART','2','Ashby','12-May-2003 11:43:03');
insert into buslog values (
1, 'BART','3','MacArth','12-May-2003 11:06:56');
insert into buslog values (
3, 'BART','3','MacArth','12-May-2003 11:23:27');
insert into buslog values (
5, 'BART','3','MacArth','12-May-2003 11:25:36');
insert into buslog values (
7, 'BART','3','MacArth','12-May-2003 11:53:16');


create or replace function prevtime(integer,text, timestamptz)
returns timestamp as
'
   if args[0] == 1:
      SD["curr"] = None
      SD["prev"] = None
      SD["currstop"] = None
      return "12/31/1959"
   else:
		if SD["currstop"] == None:
			SD["currstop"] = args[1]
			SD["curr"] = args[2]
			return None

		if args[1] != SD["currstop"]:
			SD["currstop"] = args[1]
			SD["curr"] = args[2]
			return None

		SD["prev"] = SD["curr"]
		SD["curr"] = args[2]
   return SD["prev"]
' language 'plpython';

select prevtime(1,NULL, NULL);
select bus, stop, atime, ptime, atime - ptime as headway
from 
(select bus, stop, atime, prevtime(0, stop, atime) as ptime 
from buslog where route = 'BART'
group by sord, atime, bus, stop) foo;