PostgreSQL笔记-PL/pgsql的简述以及案例分析

PL/pgsql简介：

pl/pgsql的强大之处在于过程化sql语言，然后在此基础上可以动态拼接执行sql。

tips：基础语法可以参考下面的参考

案例：

需求：将某一张表的部分数据迁移到另一张表中
sql代码：

do $body$
declare
    R RECORD;
   timeZoneSet varchar[];
    executeSQL varchar(400);
   lookupArr varchar[] :=array [['UTC-11:00', 'Pacific/Midway'],['UTC-10:00', 'Pacific/Honolulu'],['UTC-09:00', 'Pacific/Gambier'],['UTC-08:00', 'Pacific/Pitcairn'],['UTC-07:00', 'America/Creston'],['UTC-06:00', 'America/Costa_Rica'],['UTC-05:00', 'America/Bogota'],['UTC-04:00', 'Chile/Continental'],['UTC-03:00', 'America/Mendoza'],['UTC-02:00', 'America/Noronha'],['UTC-01:00', 'Atlantic/Cape_Verde'],['UTC', 'Africa/Abidjan'],['UTC+01:00', 'Africa/Algiers'],['UTC+02:00', 'Africa/Blantyre'],['UTC+03:00', 'Africa/Asmara'],['UTC+04:00', 'Asia/Dubai'],['UTC+05:00', 'Asia/Karachi'],['UTC+06:00', 'Asia/Thimbu'],['UTC+7:00', 'Asia/Saigon'],['UTC+08:00', 'Asia/Hong_Kong'], ['UTC+09:00', 'Asia/Tokyo'], ['UTC+10:00', 'Asia/Vladivostok'], ['UTC+11:00', 'Asia/Magadan'], ['UTC+12:00', 'Antarctica/McMurdo'], ['UTC+13:00', 'Pacific/Apia'], ['UTC+14:00', 'Pacific/Kiritimati']];
   timeZoneId varchar(20);
    
begin
	for R in (select * from cnt_domain where time_zone is not null and id not like '****%') loop 
--		raise notice 'R.timezone: %',R.time_zone;
		FOREACH timeZoneSet SLICE 1 in array lookupArr loop
		    IF R.time_zone = timeZoneSet[1] then
			timeZoneId := timeZoneSet[2];
		    end if;
		end loop;
		executeSQL := 'insert into cnt_domain_attribute (id, domain_id, key, value, hub_domain_id,description ,category ,revision ,entity_version , type ,is_for_reference) Values(sys_guid(),'''||R.id||''', ''ui.format.timezone'','''||timeZoneId||''' , '''||R.id||''',''NewUI_oldui timezone area by domain_id'',''UI Settings'',0,1,1,''0'')';
		raise notice 'executeSQL: %', executeSQL;
--		EXECUTE executeSQL;
    end loop;
end;
$body$ language PLPGSQL;

参考：

• https://www.cnblogs.com/wangzhen3798/p/7630597.html
• https://blog.csdn.net/lewky_liu/article/details/79352220
• https://baike.baidu.com/item/pl%2Fpgsql/20245377?fr=aladdin#1