By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,039 Members | 1,831 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,039 IT Pros & Developers. It's quick & easy.

I keep getting "type does not exist" on compile of this SETOF function (list 2 table)

P: 4
Expand|Select|Wrap|Line Numbers
  1.  
  2. BEGIN;
  3.  
  4. DROP TYPE structure.format_list2table_rs CASCADE;
  5.  
  6. CREATE TYPE structure.format_list2table_rs AS (
  7.   "item" VARCHAR(4000)
  8. );
  9.  
  10. END;
  11.  
  12. CREATE OR REPLACE FUNCTION structure.format_list2table (
  13.   "v_list" varchar,
  14.   "v_delim" varchar
  15. )
  16. RETURNS SETOF structure.format_list2table_rs AS
  17. $body$
  18. /*
  19. select * from Format_List2Table('1', '1');
  20. SELECT item FROM Format_List2Table('first||2nd||III||1+1+1+1','||');
  21. SELECT CAST(item AS INT) AS Example2 FROM Format_List2Table('111,222,333,444,555',',');
  22. 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,',',');
  23. SELECT * FROM Format_List2Table('1988,1390',',');
  24. SELECT * FROM Format_List2Table('1988',',');        
  25. SELECT * FROM Format_List2Table('1988 1390 5151 5i7151 515545',' ');
  26. */
  27.  
  28. DECLARE
  29.    v_item  VARCHAR(4000);
  30.    v_Pos  INTEGER;
  31.    v_RunLastTime  INTEGER;
  32.    SWV_List VARCHAR(4000);
  33.    SWV_Rs format_list2table_rs;
  34.  
  35. BEGIN
  36.  --  SWV_List := v_List;
  37.    BEGIN
  38.       CREATE GLOBAL TEMPORARY TABLE tt_PARSEDLIST
  39.                 (item VARCHAR(4000)) WITH OIDS;
  40.       exception when others then truncate table tt_PARSEDLIST;
  41.    END;
  42.    SWV_List := v_list;
  43.    v_RunLastTime := 0;
  44.    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
  45.    v_Pos := POSITION(v_delim IN SWV_List);
  46.    WHILE v_Pos > 0 LOOP
  47.       v_item := LTRIM(RTRIM(SUBSTR(SWV_List,1,v_Pos -1)));
  48.       IF v_item <> '' THEN 
  49.                     INSERT INTO tt_PARSEDLIST(item)
  50.                         VALUES(CAST(v_item AS VARCHAR(4000)));
  51.       ELSE
  52.          INSERT INTO tt_PARSEDLIST(item)
  53.                         VALUES(NULL);
  54.       END IF;
  55.       SWV_List := SUBSTR(SWV_List,length(SWV_List) -ABS(LENGTH(SWV_List) -v_Pos)+1);
  56.       v_Pos := POSITION(v_delim IN SWV_List);
  57.       IF SWV_List = '' THEN v_Pos = null;
  58.       END IF;
  59.       IF v_Pos = 0 AND v_RunLastTime <> 1 then
  60.          v_RunLastTime := 1;
  61.          v_Pos := LENGTH(SWV_List)+1;
  62.       END IF;
  63.    END LOOP;
  64.  
  65.    FOR SWV_Rs IN(SELECT * FROM  tt_PARSEDLIST) LOOP
  66.       RETURN NEXT SWV_Rs;
  67.    END LOOP;
  68.    RETURN;
  69. END;
  70. $body$
  71. LANGUAGE 'plpgsql'
  72. VOLATILE
  73. CALLED ON NULL INPUT
  74. SECURITY INVOKER
  75. ;
  76.  
Sep 13 '10 #1

✓ answered by rski

I think instead writting
Expand|Select|Wrap|Line Numbers
  1.    SWV_Rs format_list2table_rs;
  2.  
you should put
Expand|Select|Wrap|Line Numbers
  1.    SWV_Rs structure.format_list2table_rs;
  2.  
here

Share this Question
Share on Google+
2 Replies


Expert 100+
P: 700
I think instead writting
Expand|Select|Wrap|Line Numbers
  1.    SWV_Rs format_list2table_rs;
  2.  
you should put
Expand|Select|Wrap|Line Numbers
  1.    SWV_Rs structure.format_list2table_rs;
  2.  
here
Sep 13 '10 #2

P: 4
yep i was missing the schema info before the RS declaration.
thanks much!
Sep 14 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.