Instr function doesnt work

[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

Could you please help me?Thank you.

 

[ceco]

use position() not instr(), by the way, where did you get it from

l_idx := position(pdel in l_list);

also

there is already function in postgresql to do something like this -
string_to_array()

for the string functions you can see

http://www.postgresql.org/docs/8.2/interactive/functions-string.html

for the array functions -

http://www.postgresql.org/docs/8.2/interactive/functions-array.html