PostgreSql Procedure copy split string to table, postgresqlsplit

PostgreSql Procedure copy split string to table, postgresqlsplit

In my scenario, there is a table with a field storing data using split string.

Although Hibernate provides org. hibernate. annotations. Type annotations, You can implement org. hibernate. usertype. UserType to convert the result data, but it is not convenient to use.

I replaced this data with a mapping table. Of course, there is no problem with new data. For historical data, procedure is required for synchronization. Here is a procedure.

It mainly references PostgreSql's regexp_split_to_table function.

Regexp_split_to_table (StringText,Pattern Text[,FlagsText])

Setof text

SplitStringUsing 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)

It can be found from the API description that this function can split rule strings such as 1, 2, and 3 with commas as separators.

Let's take a look at the function effect.

It just meets the expected results and provides full 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;';BEGINDELETE FROM tableB;FOR r IN EXECUTE sqlStr LOOPIF("length"(r.refId) > 0 AND r.refId != ',')THENINSERT 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();

Question about the split method in the String class?

String [] split (String regex)
Splits the string based on the matching of the given regular expression.

In a regular expression, if the expression matches any character, it must be escaped.

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

String split () Usage

The java. lang package contains the String. split () method, and an array is returned.
I have used some of them in the application. I will summarize them for your reference only:
1. If ". ", must be written as follows: String. split ("\\. "), in this way, the correct separation is not allowed using String. split (". ");
2. If "|" is used as the separator, it must be written as follows: String. split ("\ |"), in order to correctly separate, cannot use String. split ("| ");
"." And "|" are escape characters and must be added "\\";
3. If a string contains multiple delimiters, you can use "|" as a hyphen, for example, "acount =? And uu =? Or n = ?", Use String. split ("and | or") to separate all three ");
When the String. split method is used to separate strings, if some special characters are used as separators, the expected results may not be obtained.
Let's take a look at the jdk doc description.

Public String [] split (String regex) Splits this string around matches of the given regular expression. the regex parameter is a regular-expression matching mode instead of a simple String. It may produce unexpected results for some special characters, for example, test the following code:

Use vertical bars | to separate strings and you will not get the expected results

String [] aa = "aaa | bbb | ccc". split ("| ");
// String [] aa = "aaa | bbb | ccc". split ("\\|"); in this way, the correct result can be obtained.

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

Running a string separated by vertical bars will throw a java. util. regex. PatternSyntaxException, as is the case with the plus sign +.

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]);
}

Obviously, + * is not a valid Regular Expression for pattern matching. You can escape it with "\ *" "\ +" to get the correct result.

"|" The separator string can be executed, but it is not the expected purpose. "\ |" escape to get the correct result.

If you want to use the "\" character in the string, you also need to convert it to the full text of ......>