Large SQL Examples

get_plan_score

CREATE OR REPLACE FUNCTION get_plan_score(faculty_id integer, from_date date, until_date date)
  RETURNS TABLE(
		fdate date ,
		udate date ,
		score int  ) AS
$BODY$
    DECLARE res integer;
    DECLARE unit integer;
	DECLARE StartDate DATE;
	DECLARE EndofYear DATE;
	DECLARE EndDate DATE;
BEGIN
    StartDate = from_date ::DATE ;
	CREATE TEMP TABLE IF NOT EXISTS temp_plan_score_report(user_id integer,fdate date ,udate date ,score int) ON COMMIT DROP;
	
	WHILE (StartDate <= until_date) LOOP    
	    EndofYear = date_trunc('year', StartDate)+ interval '1 year'- interval '1 day';
	    IF EndofYear > until_date THEN 
          EndDate = until_date;
	    ELSE
	        EndDate = EndofYear;
        END IF;
        SELECT SUM(general_settings_granttypeamounts.amount) INTO unit
            FROM general_settings_granttypeamounts
            WHERE general_settings_granttypeamounts.grant_type= 34;
            SELECT SUM(grant_granttransaction.amount) INTO res
            FROM grant_granttransaction
            WHERE grant_granttransaction.date_created >= date(StartDate) AND
            grant_granttransaction.date_created <= date(EndOfYear) AND grant_granttransaction.faculty_info_id = $1
            AND grant_granttransaction.type = 17
            GROUP BY grant_granttransaction.faculty_info_id;
	
            IF res IS NULL THEN
                INSERT INTO temp_plan_score_report(user_id,fdate,udate,score)
			VALUES (faculty_id,StartDate,EndDate,0);
            ELSE
                INSERT INTO temp_plan_score_report(user_id,fdate,udate,score)
			VALUES (faculty_id,StartDate,EndDate, res/unit::float);
            END IF;
		StartDate = EndDate+1;
        END LOOP;
	RETURN QUERY SELECT * FROM temp_plan_score_report where temp_plan_score_report.faculty_info_id=user_id;
	END;$BODY$
	-------------------------------------------------------------

    LANGUAGE plpgsql VOLATILE
    COST 100;
ALTER FUNCTION get_plan_score(integer, date, date)
  OWNER TO saed;
COMMENT ON FUNCTION get_plan_score(integer, date, date) IS 'calculate score for practical plans after 92 based on their granttransaction record';

-- SELECT * FROM get_plan_score(38585,'2013-02-09':: date ,'2015-05-09' :: date);
-- SELECT * FROM get_plan_score(38585,'2000-02-09':: date ,'2005-05-09' :: date);