PostgreSQL function InStr

[ZdenekMach]

Hi all, I am just trying to write stored funtion on PostgreSQL database with PG Admin III and find out a trouble.

This is my function, which shoul be use as ordinary String.Split :

-- Function: split_varchar_to_int(p_list "varchar", p_del "varchar")

-- DROP FUNCTION split_varchar_to_int(p_list "varchar", p_del "varchar");

CREATE OR REPLACE FUNCTION split_varchar_to_int(p_list varchar(30), p_del varchar(5))
RETURNS iarray AS
$BODY$declare
l_idx integer;
l_list varchar;
retValue iarray;
i integer;

begin

l_list := p_list;
i := 1;

loop

l_idx := instr(1,l_list,p_del,1);
if l_idx > 0 then
retValue := (substr(l_list,1,l_idx-1));
l_list := substr(l_list,l_idx+length(p_del));

else
retValue := (l_list);
exit;
i := i + 1;
end if;
end loop;
return retValue;
end;$BODY$

LANGUAGE 'plpgsql' VOLATILE;

ALTER FUNCTION split_varchar_to_int(p_list "varchar", p_del "varchar") OWNER TO postgres;


And this is how I call this function:

split_varchar_to_int('1;2;3;4;5', ';');

And this message is given to me:

ERROR: function instr(character varying, character varying) does not exist
No function matches the given name and argument types. You may need to add explicit type casts.
SQL statement "SELECT instr( $1 , $2 )"
PL/pgSQL function "split_varchar_to_int" line 14 at assignment

 

[AlexCuse]

This forum is for Microsoft SQL Server. While someone in here COULD know the answer to your question, I think you would be better served to post your question here: forum699

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom