PostgreSql Procedure copy split string to table，postgresqls

PostgreSql Procedure copy split string to table，postgresqlsplit

我的场景是这样，有一张表，里面有一个字段采用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();

String[] split(String regex)
根据给定的正则表达式的匹配来拆分此字符串。

而正则表达式中 . 表示匹配任意字符，需要对其转义

String[] dotR = s.split("\\.");
 

在java.lang包中有String.split()方法，返回是一个数组
我在应用中用到一些，给大家总结一下，仅供大家参考：
1、如果用“.”作为分隔的话，必须是如下写法：String.split("\\."),这样才能正确的分隔开，不能用String.split(".");
2、如果用“|”作为分隔的话，必须是如下写法：String.split("\\|"),这样才能正确的分隔开，不能用String.split("|");
“.”和“|”都是转义字符，必须得加"\\";
3、如果在一个字符串中有多个分隔符，可以用“|”作为连字符，比如：“acount=? and uu =? or n=?”,把三个都分隔出来，可以用String.split("and|or");
使用String.split方法分隔字符串时，分隔符如果用到一些特殊字符，可能会得不到我们预期的结果。
我们看jdk doc中说明

public String[] split(String regex) Splits this string around matches of the given regular expression. 参数regex是一个 regular-expression的匹配模式而不是一个简单的String，他对一些特殊的字符可能会出现你预想不到的结果，比如测试下面的代码：

用竖线 | 分隔字符串，你将得不到预期的结果

String[] aa = "aaa|bbb|ccc".split("|");
//String[] aa = "aaa|bbb|ccc".split("\\|"); 这样才能得到正确的结果

for (int i = 0 ; i <aa.length ; i++ ) {
System.out.println("--"+aa[i]);
}

用竖 * 分隔字符串运行将抛出java.util.regex.PatternSyntaxException异常，用加号 + 也是如此。

String[] aa = "aaa*bbb*ccc".split("*");
//String[] aa = "aaa|bbb|ccc".split("\\*"); 这样才能得到正确的结果

for (int i = 0 ; i <aa.length ; i++ ) {
System.out.println("--"+aa[i]);
}

显然，+ * 不是有效的模式匹配规则表达式，用"\\*" "\\+"转义后即可得到正确的结果。

"|" 分隔串时虽然能够执行，但是却不是预期的目的，"\\|"转义后即可得到正确的结果。

还有如果想在串中使用"\"字符，则也需要转......余下全文>>