array function problem

Started by tuanhoanganhabout 18 years ago2 messagesgeneral
Jump to latest
#1tuanhoanganh
hatuan05@gmail.com

I have a array function
CREATE OR REPLACE FUNCTION "temp".rowfromarray(text[])
RETURNS SETOF text AS
$BODY$DECLARE
_return text;
BEGIN
for i in 1..array_upper($1,1) loop
_return := $1[i];
return next _return;
end loop;
return;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION "temp".rowfromarray(text[]) OWNER TO postgres;

I call it by command
select temp.rowfromarray(string_to_array('1,2,3,4,5', ','));
but it have error

ERROR: set-valued function called in context that cannot accept a set
CONTEXT: PL/pgSQL function "rowfromarray" line 6 at RETURN NEXT

********** Error **********

ERROR: set-valued function called in context that cannot accept a set
SQL state: 0A000
Context: PL/pgSQL function "rowfromarray" line 6 at RETURN NEXT

When i debug it by pass {1,2,3,4,5} to first parameter, it run well.

Please help me.
Thanks you very much. Sorry for my English.

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: tuanhoanganh (#1)
Re: array function problem

tuanhoanganh <hatuan05@gmail.com> writes:

I call it by command
select temp.rowfromarray(string_to_array('1,2,3,4,5', ','));
but it have error
ERROR: set-valued function called in context that cannot accept a set

You need to say
select * from temp.rowfromarray(string_to_array('1,2,3,4,5', ','));

plpgsql functions returning SETOF have to be called within the FROM
clause of a SELECT.

regards, tom lane