Expand|Select|Wrap|Line Numbers
- BEGIN;
- DROP TYPE structure.format_list2table_rs CASCADE;
- CREATE TYPE structure.format_list2table_rs AS (
- "item" VARCHAR(4000)
- );
- END;
- CREATE OR REPLACE FUNCTION structure.format_list2table (
- "v_list" varchar,
- "v_delim" varchar
- )
- RETURNS SETOF structure.format_list2table_rs AS
- $body$
- /*
- select * from Format_List2Table('1', '1');
- SELECT item FROM Format_List2Table('first||2nd||III||1+1+1+1','||');
- SELECT CAST(item AS INT) AS Example2 FROM Format_List2Table('111,222,333,444,555',',');
- SELECT item FROM Format_List2Table('12/1/2009, 12/2/2009, 12/3/2009, 12/4/2009, 12/7/2009, 12/8/2009,, 12/9/2009, 12/10/2009, 12/11/2009,',',');
- SELECT * FROM Format_List2Table('1988,1390',',');
- SELECT * FROM Format_List2Table('1988',',');
- SELECT * FROM Format_List2Table('1988 1390 5151 5i7151 515545',' ');
- */
- DECLARE
- v_item VARCHAR(4000);
- v_Pos INTEGER;
- v_RunLastTime INTEGER;
- SWV_List VARCHAR(4000);
- SWV_Rs format_list2table_rs;
- BEGIN
- -- SWV_List := v_List;
- BEGIN
- CREATE GLOBAL TEMPORARY TABLE tt_PARSEDLIST
- (item VARCHAR(4000)) WITH OIDS;
- exception when others then truncate table tt_PARSEDLIST;
- END;
- SWV_List := v_list;
- v_RunLastTime := 0;
- SWV_List := CASE POSITION(v_delim IN SWV_List) WHEN 0 THEN coalesce(SWV_List,'') || coalesce(v_delim,'') ELSE SWV_List END; --fix lists with only 1 item
- v_Pos := POSITION(v_delim IN SWV_List);
- WHILE v_Pos > 0 LOOP
- v_item := LTRIM(RTRIM(SUBSTR(SWV_List,1,v_Pos -1)));
- IF v_item <> '' THEN
- INSERT INTO tt_PARSEDLIST(item)
- VALUES(CAST(v_item AS VARCHAR(4000)));
- ELSE
- INSERT INTO tt_PARSEDLIST(item)
- VALUES(NULL);
- END IF;
- SWV_List := SUBSTR(SWV_List,length(SWV_List) -ABS(LENGTH(SWV_List) -v_Pos)+1);
- v_Pos := POSITION(v_delim IN SWV_List);
- IF SWV_List = '' THEN v_Pos = null;
- END IF;
- IF v_Pos = 0 AND v_RunLastTime <> 1 then
- v_RunLastTime := 1;
- v_Pos := LENGTH(SWV_List)+1;
- END IF;
- END LOOP;
- FOR SWV_Rs IN(SELECT * FROM tt_PARSEDLIST) LOOP
- RETURN NEXT SWV_Rs;
- END LOOP;
- RETURN;
- END;
- $body$
- LANGUAGE 'plpgsql'
- VOLATILE
- CALLED ON NULL INPUT
- SECURITY INVOKER
- ;