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

This article mainly explains "what is a PG stored procedure". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what is a PG stored procedure".

-- delete functions drop function if exists upgrade_task_device (integer); create or replace function upgrade_task_device (exec_way integer) returns integer as$$declare cron_prefix varchar default'0 * /'; cron_suffix varchar default'*?'; cron_period varchar; tmp_cron_period integer; cron_exp varchar default''; cur_row record; ret integer default 0 Task_cursor cursor (exec_way integer) for select t.* from task t where t.execution_way = exec_way;begin-- if time_period > 59, set the value to 59 update task set time_period = 59 where time_period is not null and time_period > 59; open task_cursor (exec_way) Loop fetch task_cursor into cur_row; exit when not FOUND -- scheduled execution | execute immediately if cur_row.execution_way = 1 or cur_row.execution_way = 2 then update task_device set task_type = cur_row.task_type, task_name = cur_row.task_name, retry = cur_row.retry, start_time=cur_row.start_time End_time=cur_row.end_time,-- the execution mode is all changed to 'periodic execution' execution_way = 3, create_user_id=cur_row.create_user_id where task_id = cur_row.id and time_rule is null Elsif cur_row.execution_way = 3 then-periodic execution tmp_cron_period = cur_row.time_period; if tmp_cron_period > 59 then tmp_cron_period = 59; end if -- convert to string concatenation cron_period = TRIM (to_char (tmp_cron_period, '99'));-- cron concatenation cron_exp = cron_prefix | | cron_period | | cron_suffix Update task_device set task_type = cur_row.task_type, task_name = cur_row.task_name, retry = cur_row.retry, start_time=cur_row.start_time, end_time=cur_row.end_time, time_rule=cron_exp -- the execution mode is all changed to 'periodic execution' execution_way = 3, create_user_id=cur_row.create_user_id where task_id = cur_row.id and time_rule is null End if; end loop; close task_cursor; ret = 1; return ret;end;$$ language plpgsql;-- deals with timed execution, periodic execution, immediate execution select upgrade_task_device (1); select upgrade_task_device (2); select upgrade_task_device (3);-- delete function drop function if exists upgrade_task_device (integer) Thank you for your reading, the above is the content of "what is the PG stored procedure", after the study of this article, I believe you have a deeper understanding of what the PG stored procedure is, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.