-- Function: sp_get_form_bag_content(bigint, bpchar)
-- DROP FUNCTION sp_get_form_bag_content(bigint, bpchar);
CREATE OR REPLACE FUNCTION sp_get_form_bag_content(in_cand_id bigint, in_payment_mode bpchar)
RETURNS SETOF typ_form_bag_content AS
$BODY$
DECLARE
v_form_id_arr _int2;
v_rec form_bag%ROWTYPE;
v_return_rec typ_form_bag_content;
v_index int2;
BEGIN
update cand_preferences
set payment_mode = in_payment_mode
where cand_id = in_cand_id
and payment_mode <> in_payment_mode;
v_index=0;
for v_rec in select form_id from form_bag where cand_id=in_cand_id
loop
v_form_id_arr[v_index]= v_rec.form_id;
v_index = v_index + 1;
raise notice '%', v_form_id_arr[0];
end loop;
for v_return_rec in
select temp2.form_id, college_name, course_list, form_cost, warning, sp_format_date(last_date,'DD-Mon-YYYY, DOW'), is_exam from (
select college_id, temp.form_id, course_list, form_cost, warning, last_date, is_exam from
(
select sp_warnings_cost.form_id, course_list, warning, form_cost from
(
select sp_cost.form_id, coalesce(sp_warnings.warning,'') as warning, sp_cost.form_cost from
(
select form_id,
array_to_string(array_accum(warnings), '::') as warning
from (
select *
from sp_check_form_constraints(in_cand_id, v_form_id_arr)
) as sp1_warnings
group by form_id
) as sp_warnings
right outer join sp_get_forms_cost(in_cand_id, v_form_id_arr) as sp_cost on sp_cost.form_id=sp_warnings.form_id
) as sp_warnings_cost
left outer join sp_get_form_bag_courses(in_cand_id) sp_courses on sp_warnings_cost.form_id=sp_courses.form_id
) as temp
inner join forms f on f.form_id=temp.form_id
)temp2
inner join colleges col on col.college_id=temp2.college_id
loop
return next v_return_rec;
end loop;
return;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION sp_get_form_bag_content(bigint, bpchar) OWNER TO postgres;
Comment