Обсуждение: 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)
От
"Jonathan Brinkman"
Дата:
[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)
От
Merlin Moncure
Дата:
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)
От
"Jonathan Brinkman"
Дата:
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