CODEWITHSUNDEEP
sqlpostgresqlstored-procedures
user8545255
user8545255

Reputation: 839

Run stored procedures in a loop based on a parameter

I have a function test_function where I am passing _snapdt as parameter. I want to run this function for several dates (_snapdt) individually in a loop.

Can someone help me with this?

For example I want to run this function for several dates like "2018-01-30" , 2018-02-30" and so on ..

Currently I am running it manually like this:

select * from test_function('2018-01-30') ;
select * from test_function('2018-02-30') ;

Here is the function 

CREATE OR REPLACE FUNCTION test_function(_snapdt date) 
RETURNS integer
AS 
$BODY$
declare rows integer;
BEGIN

DROP TABLE IF EXISTS temp_table;
create table temp_table AS
select col1,col2 
    from table1
    where id=01000 and
    snapshot_date=_snapdt
group by col1,col2
distributed randomly;


INSERT INTO standard_table
(   snap_date,
    col1,
    col2
)
SELECT 
_snapdt as snap_date,
a.col1,
a.col2,
FROM temp_table a;

GET DIAGNOSTICS rows=ROW_COUNT;
DROP TABLE IF EXISTS temp_table;
return rows;
END;
$BODY$
LANGUAGE plpgsql;

Upvotes: 0

Views: 40

Answers (1)

user330315
user330315

Reputation:

use generate_series()

select test_function(t.dt::date)
from generate_series(date '2018-01-30', date '2018-02-28') as t(dt);

If you want to call the function once for each month, change the interval:

select test_function(t.dt::date)
from generate_series(date '2018-01-01', date '2019-12-01', interval '1 month') as t(dt);

Upvotes: 3

Related Questions