CREATE OR REPLACE FUNCTION get_film (p_pattern VARCHAR) 
 RETURNS TABLE (
 film_title VARCHAR,
 film_release_year INT
) 
AS $$
BEGIN
 RETURN QUERY SELECT
 title,
 cast( release_year as integer)
 FROM
 film
 WHERE
 title ILIKE p_pattern ;
END; $$ 
 
LANGUAGE 'plpgsql';

SELECT
 *
FROM
 get_film ('Al%');

SELECT
 get_film ('Al%');

CREATE OR REPLACE FUNCTION get_film (p_pattern VARCHAR,p_year INT) 
 RETURNS TABLE (
 film_title VARCHAR,
 film_release_year INT
) AS $$
DECLARE 
 var_r record;
BEGIN
 FOR var_r IN(SELECT title, release_year 
   FROM film 
   WHERE title ILIKE p_pattern 
   AND release_year = p_year) 
 LOOP
 film_title := upper(var_r.title) ; 
 film_release_year := var_r.release_year;
 
 RETURN NEXT;
 END LOOP;
END; $$ 
LANGUAGE 'plpgsql';

SELECT
 *
FROM
 get_film ('%er', 2006);

CREATE OR REPLACE FUNCTION fun_get_real_inv_qty(pvOrderId varchar)
 RETURNS SETOF record AS
$BODY$begin
--drop table if exists tmp_1 ;
--create temp table tmp_1 as 
return query 
	select fp_prod_id,fq_part_no,fq_name, 
	sum(case when fo_type='P' then -fp_qty 	 
		 else 0
	  end 
	) as purchase_qty,
	sum(case when fo_type='S' then -fp_qty 	 
		 else 0
	  end 
	) as saleqty,
	sum(case when fo_type='S' then -fp_qty 
		 when fo_type='P' then fp_qty 
		 else 0
	  end ) as surplus_qty from tp_send_det,to_send_note,tq_prod_mstr
	where fp_order_id=pvOrderId and fo_note_id=fp_note_id and fq_prod_id=fp_prod_id and fq_type='I'
	group by fp_prod_id,fq_part_no,fq_name ;
end;$BODY$
 LANGUAGE plpgsql VOLATILE
 COST 100
 ROWS 10;
ALTER FUNCTION fun_get_real_inv_qty()
 OWNER TO postgres;

select * from fun_get_real_inv_qty('D302') f(fp_prod_id bigint,fq_part_no varchar ,fq_name varchar ,purchase_qty numeric ,saleqty numeric ,surplus_qty numeric );