Snippet revisions — Bitbucket

+-- Function: public.calculateexperiencenew(integer)

+-- DROP FUNCTION public.calculateexperiencenew(integer);

+CREATE OR REPLACE FUNCTION public.calculateexperiencenew(integer)

+  RETURNS integer AS

+$BODY$

+Declare

+total numeric(14,4) := 0;

+r_Experience RECORD;

+        starttime date;

+        endtime date;

+        mindate date;

+        maxdate date;

+	olddate date;

+        subtractvalue INT4 := 0;

+        diff INT4 := 0;

+        tevta_resume INT4 := 0;

+Begin

+select into mindate start_date from experience where resume_id = $1 order by start_date limit 1;

+select into maxdate end_date from experience where resume_id = $1 order by end_date desc limit 1;

+if maxdate is null then

+     select into diff current_date - mindate;

+     total = diff;

+else

+     select into diff maxdate - mindate;

+     total = diff;

+end if;

+for r_Experience IN select start_date,end_date from experience where resume_id = $1 order by start_date loop

+endtime = r_Experience.end_date;

+starttime  = r_Experience.start_date;

+tevta_resume = $1;

+if endtime is null then

+	endtime = current_date;

+end if;

+if olddate is null then

+	olddate = endtime;

+end if;

+if (starttime > olddate) then

+	select into diff starttime - olddate;

+	subtractvalue = subtractvalue + diff;

+end if;

+olddate = endtime;

+END LOOP;

+        total = total - subtractvalue;

+        total = total / 365;

+        update resume set experience = total where resume_id = $1;

+        return tevta_resume;

+End;$BODY$

+  LANGUAGE plpgsql VOLATILE

+  COST 100;

+ALTER FUNCTION public.calculateexperiencenew(integer)

+  OWNER TO jawad;

Snippets