Thanks, yes the schema was missing from the DECLARE rs statement! -----Original Message----- From: Merlin Moncure [mailto:mmoncure@xxxxxxxxx] Sent: Monday, September 13, 2010 1:35 PM To: Jonathan Brinkman Cc: pgsql-general@xxxxxxxxxxxxxx Subject: Re: I keep getting "type does not exist" on compile of this SETOF function (list 2 table) On Mon, Sep 13, 2010 at 11:17 AM, Jonathan Brinkman <jonathanbrinkman@xxxxxxxxx> wrote: > [CODE] > > 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 > ; is 'structure' in your search_path? in the declare section you didn't prefix w/namespace: > SWV_Rs format_list2table_rs; but you did everywhere else. merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
