Postgresql dynamic cursor use case
CREATE or REPLACE FUNCTION insert_p_date () RETURNS text as ```
$
DECLARE
Sql_string text
Sdate timestamp without time zone
Ldate timestamp without time zone
_ wtid integer
The first day of each month for a period of time
Cur1 cursor for select date (zz) from generate_series (date_trunc ('month',to_date (' 20171001)), date_trunc ('month',to_date (' 20180401)),'1 month') as tt (zz)
Curs2 refcursor
BEGIN
-- Open it
Open cur1
Loop
Fetch cur1 into sdate
If there is no data, end the loop
Exit when NOT found
-- the last day of the last month of a period of time
Select (date_trunc ('month',sdate) + INTERVAL' 1 MONTH-1 day') into ldate
The last day of last month
-- select date_trunc ('day', date_trunc (' month',sdate))-interval'1 day' into ldate
-- breakpoint thrown on the first day of the month
Raise notice 'sdate=%', sdate
-- define all individual devices within a month
Open curs2 for select distinct wtid from statisticdata_bak where rectime between sdate and ldate
Loop
-- take records
Fetch curs2 into _ wtid
If no data is retrieved, end the loop
Exit when NOT found
-- throw out device information
Raise notice'_ wtid=%', _ wtid
-- perform data import by device every month
Sql_string: = 'insert into statisticdata select * from statisticdata_bak where wtid=' | | _ wtid | |' and rectime between''| | sdate | |''and''| | ldate | |';'
Execute sql_string
Raise notice 'execution complete =%', _ wtid
End loop
Close curs2
End loop
-- close cursor 1
Close cur1
-- return the result
RETURN 'Import successful!'
END
$
LANGUAGE plpgsql
