calling a function in sql within nested loop

Question

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;
$$

What does your datavault.account_active_for_date function do? Why does it take two records as parameters? Does it return anything that you want to get as a result? — Bergi
– Bergi, Commented Oct 26, 2021 at 18:23
it returns bool, if the account was active for a particular date — owen gerig
– owen gerig, Commented Oct 26, 2021 at 18:25

Stefanov.sm · Accepted Answer · 2021-10-26 18:13:07Z

1

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.

edited Oct 26, 2021 at 18:13

answered Oct 26, 2021 at 17:59

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

calling a function in sql within nested loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related