postgresql

-- search column name
 select table_name, column_name from information_schema.columns where column_name like '%current_interest_rate%'
 and table_name in ('identifies','tracks','pages');
 
 -- stored procedure
 CREATE OR REPLACE FUNCTION findanonid(IN userId varchar, IN internalUserId varchar) 
RETURNS TABLE(anonymousid varchar) 
AS $$
BEGIN
   return query																 
   select distinct(anonymous_id) from feb_impression_attribs where anonymous_id is not null and (user_id = userId or internal_user_id = internalUserId);
END;
$$ LANGUAGE plpgsql


#Fold attributes
CREATE OR REPLACE FUNCTION fold_attribute(IN rownum int, IN anonId varchar) 
returns void AS $$
declare
  col varchar;
  cols varchar[] := ARRAY['assetname_customer_platformv2','assetname_service_platform','assetname_customer_platform','assetname_loanscore','assetname_fastrefi','assetname_homeloanreport','
current_interest_rate','final_loan','final_lvr','income','loan_purpose_owner_occupied','loan_purpose_investment','lir','loantype_purchase','loantype_refinance','loantype_construction','
loan_value','loan_score','lvr','max_borrowing_power','potential_savings','postcode','property_value','internal_application_id'];
BEGIN															 
	FOREACH col IN ARRAY cols
	LOOP
	 RAISE NOTICE 'Counter: %', col;
	 EXECUTE format('UPDATE feb_impression_attribs_result3
	 				 SET %1$s=subquery.%1$s
					 FROM (SELECT %1$s FROM feb_impression_attribs_result3 where anonymous_id = %2$L and row_num = %4$L) AS subquery
					 WHERE feb_impression_attribs_result3.%1$s is null 
					 and feb_impression_attribs_result3.row_num = %3$L',col,anonId,rownum,rownum-1);
    END LOOP;
END;
$$ LANGUAGE plpgsql;

## Loop and call function
do $$
  declare
    counter int;
  	max int;
    arow record;
    foo varchar(50);
  begin
    for arow in
      select distinct(anonymous_id) from feb_impression_attribs_result3
    loop
	  counter := 2;
	  for max in
	  	select max(row_num) from feb_impression_attribs_result3 where anonymous_id = arow.anonymous_id
	  loop
		WHILE counter <= max LOOP
		RAISE NOTICE 'doing in loop id: %, row: %', arow.anonymous_id, counter;
		-- execute update logic here
		perform fold_attribute(counter, arow.anonymous_id);
		counter := counter + 1 ;
		END LOOP ; 
	  end loop;
    end loop;
  end;
$$;