Check the below DB function. You can dynamically manage your DB function parameters by using this small tactic. Simply you need to pass your list of parameters as a JSON object. Here, It converts to the relevant parameters inside the function by the following step:
How the process works
json_data := lower(_value)::json;
_itc_id := coalesce((json_data->>’itcid’)::bigint,-1)::bigint;
_itc_company := coalesce((json_data->>’itccompany’)::bigint,-1)::bigint;
_itc_type := coalesce((json_data->>’itctype’)::bigint,-1)::bigint;
_itc_level := coalesce((json_data->>’itclevel’)::bigint,-1)::bigint;
_itc_parent := coalesce((json_data->>’itcparent’)::bigint,-1)::bigint;
_itc_category := coalesce((json_data->>’itccategory’),”)::varchar(200);
_itc_status := coalesce((json_data->>’itcstatus’)::bigint,-1)::bigint;
_itc_active := coalesce((json_data->>’itcactive’)::bigint,-1)::bigint;
Full Function Sample:
CREATE OR REPLACE FUNCTION master.flt_fm_item_category(_value text default ‘{}’::text)
RETURNS SETOF master.flt_vm_item_category
LANGUAGE plpgsql
AS $function$
declare
json_data JSON;
_itc_id bigint := -1;
_itc_company bigint := -1;
_itc_type bigint := -1;
_itc_level bigint := -1;
_itc_parent bigint := -1;
_itc_category varchar(200) := ”;
_itc_status bigint := -1;
_itc_active bigint := -1;
begin
— Cast the text to JSON
json_data := lower(_value)::json;
_itc_id := coalesce((json_data->>’itcid’)::bigint,-1)::bigint;
_itc_company := coalesce((json_data->>’itccompany’)::bigint,-1)::bigint;
_itc_type := coalesce((json_data->>’itctype’)::bigint,-1)::bigint;
_itc_level := coalesce((json_data->>’itclevel’)::bigint,-1)::bigint;
_itc_parent := coalesce((json_data->>’itcparent’)::bigint,-1)::bigint;
_itc_category := coalesce((json_data->>’itccategory’),”)::varchar(200);
_itc_st atus := coalesce((json_data->>’itcstatus’)::bigint,-1)::bigint;
_itc_active := coalesce((json_data->>’itcactive’)::bigint,-1)::bigint;
return query
SELECT ca.itc_id, ca.itc_code, ca.itc_reference, ca.itc_company, ca.itc_type, ”::text itc_type_info,
ca.itc_level, fvk.key_value1 itc_level_info, ca.itc_parent, coalesce(ca2.itc_category,’N//A’) itc_parent_info,
ca.itc_category, ca.itc_description,
ca.itc_status, st.key_value1 itc_status_info,
ca.itc_active, ac.key_value1 itc_active_info,
ca.created_by, ca.created_date, ca.last_modified_by, ca.last_modified_date
FROM master.flt_m_item_category ca
inner join master.flt_vm_keyvalues fvk on fvk.key_id = itc_level and fvk.key_key = ‘item_cat_level’
inner join master.flt_vm_keyvalues st on st.key_id = itc_status and st.key_key = ‘status’
inner join master.flt_vm_keyvalues ac on ac.key_id = itc_active and ac.key_key = ‘active’
left join master.flt_m_item_category ca2 on ca2.itc_id = ca.itc_parent
where 1 = 1
AND (_itc_id = -1 OR ca.itc_id = _itc_id)
AND (_itc_company = -1 OR ca.itc_company = _itc_company)
AND (_itc_type = -1 OR ca.itc_type = _itc_type)
AND (_itc_level = -1 OR ca.itc_level = _itc_level)
AND (_itc_parent = -1 OR ca.itc_parent = _itc_parent)
AND (_itc_category = ” OR ca.itc_category ILIKE CONCAT(‘%’, _itc_category, ‘%’))
AND (_itc_status = -1 OR ca.itc_status = _itc_status)
AND (_itc_active = -1 OR ca.itc_active = _itc_active);
end
$function$
;
