Thread: I keep getting "type does not exist" on compile of this SETOF function (list 2 table)
I keep getting "type does not exist" on compile of this SETOF function (list 2 table)
From
"Jonathan Brinkman"
Date:
[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 ; [/CODE]
Re: I keep getting "type does not exist" on compile of this SETOF function (list 2 table)
From
Merlin Moncure
Date:
On Mon, Sep 13, 2010 at 11:17 AM, Jonathan Brinkman <jonathanbrinkman@yahoo.com> 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
Re: I keep getting "type does not exist" on compile of this SETOF function (list 2 table)
From
"Jonathan Brinkman"
Date:
Thanks, yes the schema was missing from the DECLARE rs statement! -----Original Message----- From: Merlin Moncure [mailto:mmoncure@gmail.com] Sent: Monday, September 13, 2010 1:35 PM To: Jonathan Brinkman Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] 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@yahoo.com> 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