PostgreSql Procedure copy split string to table

標籤：postgresql   procedure   string to array   

我的情境是這樣，有一張表，裡面有一個欄位採用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;';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();

PostgreSql Procedure copy split string to table