alter table \schema.observation set with oids;
alter table \schema.objcat drop column if exists id_cat cascade;
alter table \schema.objcat add column id_cat serial;

DROP TYPE IF EXISTS coords;
CREATE TYPE coords AS (
	raj2000 double precision,
	dej2000 double precision
	);


DROP FUNCTION IF EXISTS build_catalog(int,int,double precision);
CREATE OR REPLACE FUNCTION build_catalog (fromOID int, toOID int, radius double precision)
	RETURNS VOID AS $$
DECLARE 
	cur1 CURSOR FOR 
		SELECT 
			raj2000, dej2000
		FROM
			\schema.observation AS obs
		WHERE 
			obs.oid >= fromOID
		AND
			obs.oid < toOID;
	curr_raj2000 double precision;
	curr_dej2000 double precision;
	curr_coords_cat coords;
	cnt int;        

BEGIN 
/*SELECT current_setting('transaction_isolation') into tmp;
raise notice 'Isolation level %', tmp;*/
OPEN cur1;
cnt:=0;
LOCK TABLE \schema.objcat IN SHARE ROW EXCLUSIVE MODE;
LOOP 
	FETCH cur1 INTO curr_raj2000, curr_dej2000;
		EXIT WHEN NOT found;
	
	WITH 
		upsert
	AS
		(UPDATE 
			\schema.objcat
		SET 
			ipix_cat=q3c_ang2ipix(
				(raj2000 * weight + curr_raj2000) / (weight + 1),
				(dej2000 * weight + curr_dej2000) / (weight + 1)
			),
			raj2000 = (raj2000 * weight + curr_raj2000) / (weight + 1),
			dej2000 = (dej2000 * weight + curr_dej2000) / (weight + 1),
			weight=weight+1 
		WHERE 
			q3c_join(curr_raj2000, curr_dej2000,
				raj2000, dej2000,
				radius)
		RETURNING *),
	ins AS
	(INSERT INTO 
			\schema.objcat 
			(ipix_cat, raj2000, dej2000, weight)
		SELECT
			(q3c_ang2ipix(curr_raj2000, curr_dej2000)),
			curr_raj2000,
			curr_dej2000,
			1
	WHERE NOT EXISTS
		(SELECT * FROM upsert)
	RETURNING *)
	UPDATE 
		\schema.observation
	SET 
		id_cat = (SELECT DISTINCT
				id_cat
			FROM
				upsert
			UNION 
			SELECT 
				id_cat
			FROM
				ins
			WHERE id_cat IS NOT NULL
			LIMIT 1)
	WHERE CURRENT OF cur1;
	cnt:=cnt+1;

	IF ((cnt % 100000 ) = 0) THEN
		RAISE NOTICE 'Processed % entries', cnt;
	END IF;
		
END LOOP;
CLOSE cur1;
END;
$$ LANGUAGE plpgsql;

SELECT build_catalog(854536,880530, 2.77778e-4);