473,231 Members | 1,986 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,231 software developers and data experts.

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

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

2 2532
rski
700 Expert 512MB
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
yep i was missing the schema info before the RS declaration.
thanks much!
Sep 14 '10 #3

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

Similar topics

3
by: Steven T. Hatton | last post by:
Scroll to the bottom and read the last part first. I've been trying very diligently to 'modularize' the code from TC++PL3E found here: http://www.research.att.com/~bs/matrix.c I keep getting...
1
by: Alvey Sidecast | last post by:
Struggling even more than usual here. I've (nearly) written an XP application but have this problem where I can't delete any records from the table my subform is based on. The message I keep...
3
by: KRC | last post by:
I am using an external object called WebZinc to help parse web pages in VB.net. I am trying to use a particular method following the example in the help reference but am unable to get VB.net to...
3
by: Jon | last post by:
I'm learning about datatables. When using the example provided by MS in the ..NET Framework Class Library for DATATABLE (see below) I get an error on line 3 that says "Type expected". Is something...
1
by: frame | last post by:
Hi, I am trying to compile the following program, whose fragments are presented in Section 2.1: "Compile-Time Assertions" of Chapter 2: "Techniques" of "Modern C++ Design" by Andrei...
4
by: freeflytim | last post by:
I'm trying to implement a custom MembershipProvider (and RoleProvider) together with a custom MembershipUser class in C#, Asp.Net 2.0, MS Visual Studio 2005. Everything has worked fine so far,...
13
by: Kevin Liebowicz | last post by:
Yes, I wasted the past two days trying to fix this. Yes, this is on a Win2003 Server. Yes, this machine is a domain controller. Yes, I seen the dozens of KB articles like this one:...
3
by: kang jia | last post by:
hi, currently i am doing booking car online and one of the function is cancel and update booking. if the booking number users entered in is not exist. it will redirect the page stating that the...
3
by: bxscikid | last post by:
I am using VB 6 in order to clear a listbox (lststuff) containing material entered by the user via input boxes. I am trying to utilize a command button which will clear the list for the user. When...
0
by: jb489 | last post by:
Hi all, Hope I am posting this in the right forum. I seem to be having a problem when using serialization and web services. <b>Scenario:</b> I have built a web service which includes a...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.