calling a function in sql within nested loop-CodePudding

My query doesnt even work, but hopefully the logic comes through. Basically Im using datavault.dimdates_csv to produce a row for each date/day. Then for each day Im trying to get all account ids and for each and call a function using the date and for the account.

is there a better approach to getting my data? I know nested loops arnt that great for sql.

 do
$$
declare
    date_record record;
    account record;
begin
    for date_record in select d."date"  from datavault.dimdates_csv d
    for account in select sad.id from datavault.sat_account_details sad 
    select datavault.account_active_for_date(date_record , account)
    loop  
    loop  
    end loop;
end;
$$

CodePudding user response：

It's hard to follow your business logic but syntax-wise your block needs correction. Please note that d."date" and sad.id are scalars (I assume a date and an integer) and not records.

do
$$
declare
    running_date date;
    running_id integer;
begin
    for running_date in select d."date" from datavault.dimdates_csv d loop
        for running_id in select sad.id from datavault.sat_account_details sad loop
            perform datavault.account_active_for_date(running_date, running_id);
        end loop; 
    end loop;
end;
$$;

As far as I can see you are calling function datavault.account_active_for_date for every pair of d."date" and sad.id. If this is true then you can simply

select datavault.account_active_for_date(d."date", sad.id) 
from datavault.dimdates_csv d, datavault.sat_account_details sad;

and ignore the resultset.