PostgreSql Procedure copy split string to table

我的场景是这样，有一张表，里面有一个字段采用split string存数据。

虽然Hibernate本身提供了 org.hibernate.annotations.Type 注释，可以通过实现org.hibernate.usertype.UserType来转换这种可以结果数据，但是使用上并不方便。

为了干掉这种数据，我采用了mapping表来取代。对于新的数据当然没有问题，对于历史数据，需要procedure来同步。这里分享一个procedure。

主要是引用了 PostgreSql 的 regexp_split_to_table 函数。

regexp_split_to_table(stringtext, pattern text [, flagstext])

setof text

Split string using a POSIX regular expression as the delimiter. See Section 9.7.3 for more information.

regexp_split_to_table('hello world', E'\\s+')

hello world (2 rows)

从API描述可以发现，该函数可以将1,2,3这种规则字符串以逗号为分隔符拆分。

先来看一下函数的效果。

刚好满足我们想要的结果，提供完全的procedure。

DROP FUNCTION IF EXISTS syncRef();
CREATE OR REPLACE FUNCTION syncRef() RETURNS void AS
$BODY$
	DECLARE 
		r RECORD;
		sqlStr text := 'SELECT id,regexp_split_to_table(refIds,'','') as refId from tableA where "length"(refIds) > 0;';
	BEGIN
			DELETE FROM tableB;
			FOR r IN EXECUTE sqlStr LOOP
				IF("length"(r.refId) > 0 AND r.refId != ',')
					THEN
						INSERT INTO tableB(id,ref_id) VALUES(r.id,cast(r.refId AS BIGINT));
				END IF;
			END LOOP;
			RETURN;
	END
$BODY$
LANGUAGE plpgsql ;
ALTER FUNCTION syncRef()
  OWNER TO db;

SELECT syncRef();
DROP FUNCTION IF EXISTS syncRef();