POSTGRESQL Procedure Copy split string to table

My scene is this, there is a table with a field that uses the split string to save the data.

Although hibernate itself provides org.hibernate.annotations.Type annotations, it is possible to convert this kind of data by implementing Org.hibernate.usertype.UserType, but it is not convenient to use.

To get rid of this data, I used the mapping table instead. For the new data, of course, there is no problem, for historical data, need procedure to synchronize. Share a procedure here.

The main reference is the regexp_split_to_table function of POSTGRESQL.

Regexp_split_to_table (stringtext, pattern text [, flagstext] )

Setof text

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

Regexp_split_to_table (' Hello World ', E ' \\s+ ')

Hello World (2 rows)

From the API description, it can be found that the function can be a comma-delimited string of such rules as a delimiter split.

Let's take a look at the effect of the function.

Just to meet the results we want, to provide complete 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,c AST (R.refid as BIGINT)); END IF; END LOOP; RETURN; End$body$language Plpgsql; ALTER FUNCTION syncref ()  OWNER to DB; SELECT syncref ();D rop FUNCTION IF EXISTS syncref ();

POSTGRESQL Procedure Copy split string to table