CREATE OR REPLACE FUNCTION pcyc.mapp_func_test_dyna(mapp_tbl text,rec_type text,src_tbl text,map_cols_select text,map_tbl_col text,src_tbl_col text,conditions text,how_many integer)
RETURNS table (col character variable)
AS $func$
begin
return query
execute format('SELECT %I from pcyc.%I, pcyc.%I as t2 where %I=t2.%I %s ' , map_cols_select, mapp_tbl,src_tbl, map_tbl_col,src_tbl_col, conditions);
END;
$func$
LANGUAGE plpgsql;
and calling the function as :
select * from pcyc.mapp_func_test_dyna('tbl_mapping_profdesig','HCP','tbl_transactions_bkag','federalreportablecategory','professionaldesignation','pwc_profdesg',' and federalreportablecategory is not null')
I'm trying to retrieve multiple columns through the select statement for which im passing columns as parameters 'map_cols_select' but the line 'table (col character variable)' makes the return possible only for one column. How can i dynamically get multiple column as returns.
I've already tried returns set-of records but it doesnt work! please help me, been stuck at this for a long time!!
