POSTGRESQL存储过程（PROCEDURES）和函数（FUNCTIONS）

简介

在 pg 中存储过程和函数创建方式一样，写法都差不多
区别：
存储过程（Procedures）：无返回值（返回值为 void）的函数（function）。
函数（functions）：有返回值（返回值非 void）的函数（function）。

使用

格式

drop function if exists [函数名];
create function [函数名] ([参数]) returns [返回值] as
 $$
declare
  [变量声明]
begin
  [sql逻辑]
end;
$$
language 'plpgsql';

推荐使用 Navicat 来创建，如图：

函数使用

CREATE OR REPLACE FUNCTION "public"."update_us_businessoperationperiod"()
  RETURNS "pg_catalog"."void" AS $BODY$
	DECLARE
	-- 定义变量
	business_detail record;
	current_date_n timestamp;
	current_date_s VARCHAR;
	num int :=0;
	BEGIN
	   -- 循环需要处理的数据
	   FOR business_detail IN (SELECT * FROM "cddKycBusinessDetail" WHERE "kycId" = '7145720d-e57b-4b4e-8b97-a97662520f35') LOOP
		 -- 获取注册时间转时间戳
		 current_date_n := TO_TIMESTAMP(business_detail."businessRegistrationDate", 'YYYY-MM-DD');
		 -- 注册时间加一年
		 current_date_n := current_date_n + '1 year';
		 -- 时间戳转字符串
		 current_date_s := to_char(current_date_n, 'YYYY-MM-DD');

		 num := num + 1;
		 -- 更改有效时间
		 UPDATE "cddKycBusinessDetail" SET "businessOperationPeriod" = current_date_s WHERE "id" = business_detail."id";
		 -- 打印
		 raise notice 'cddKycBusinessDetail id:%, %, %, %.',business_detail."id", business_detail."businessRegistrationDate", current_date_s, num;
     END LOOP;
	RETURN;
  END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

CREATE FUNCTION "public"."update_rate"(account_ids text[])
	RETURNS void AS $BODY$
	DECLARE
account_id text;
	BEGIN
	-- Routine body goes here...
   FOREACH account_id IN ARRAY account_ids
    LOOP
        raise notice 'accountId:%', account_id;
    END LOOP;
	RETURN;
END$BODY$
	LANGUAGE plpgsql

-- 执行
SELECT update_rate(ARRAY['fc3827ec-58e9-4f42-a661-a3aa658c3e61'])

存储过程使用

CREATE OR REPLACE PROCEDURE "public"."update_us_businessoperationperiod_2"()
 AS $BODY$
 DECLARE
	-- 定义变量
	business_detail record;
	current_date_n timestamp;
	current_date_s VARCHAR;
	num int :=0;
 BEGIN
	-- 循环需要处理的数据
	   FOR business_detail IN (SELECT * from "cddKycBusinessDetail" where "kycId" in (SELECT "id" from "cddKyc" where "isLatest" = true and status != 'Pending') and "registrationRegion" = 'US' and "businessOperationPeriod" = '') LOOP
		 IF business_detail."businessRegistrationDate" ~ '.000z' then
		    -- 获取注册时间转时间戳 2022-05-30T16:00:00.000Z
		    current_date_n := TO_TIMESTAMP(business_detail."businessRegistrationDate", 'YYYY-MM-DDTHH24:MI:SS.000z');
		 ELSE
		    -- 获取注册时间转时间戳 2022-05-30
		    current_date_n := TO_TIMESTAMP(business_detail."businessRegistrationDate", 'YYYY-MM-DD');
		 END IF;
		 -- 注册时间加一年
		 current_date_n := current_date_n + '1 year';
		 -- 时间戳转字符串
		 current_date_s := to_char(current_date_n, 'YYYY-MM-DD');

		 num := num + 1;
		 -- 更改有效时间
		 UPDATE "cddKycBusinessDetail" SET "businessOperationPeriod" = current_date_s WHERE "id" = business_detail."id";
		 -- 打印
		 raise notice 'cddKycBusinessDetail id:%, %, %, %.',business_detail."id", business_detail."businessRegistrationDate", current_date_s, num;
     END LOOP;

END$BODY$
  LANGUAGE plpgsql

CREATE OR REPLACE PROCEDURE "public"."update_rate"()
 AS $BODY$
 DECLARE
account_ids text[];
account_id text;

 BEGIN
	-- 直接从表中查数据进行循环
	account_ids := ARRAY(SELECT "id" from account)::text[];

	FOREACH account_id IN ARRAY account_ids
    LOOP
        raise notice 'accountId:%', account_id;
    END LOOP;

END$BODY$
  LANGUAGE plpgsql

CREATE OR REPLACE PROCEDURE "public"."update_rate"()
 AS $BODY$
 DECLARE
account_ids text[];
account_id text;

 BEGIN
	-- Routine body goes here...
	account_ids := ARRAY['fc3827ec-58e9-4f42-a661-a3aa658c3e61'];

	FOREACH account_id IN ARRAY account_ids
    LOOP
        raise notice 'accountId:%', account_id;
    END LOOP;

END$BODY$
  LANGUAGE plpgsql

文档

postgresql 官方文档
postgresql raise 函数
postgresql 日期相关函数