Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.