I suppose you will want to use perform array_append(array,val) instead of select. That is the plpgsql way to do it. Must have slipped my mind. PostGresql has 2 different ways of doing the same thing depending on where you are. Using regular sql if you don't want to return any result you do a select functionname(). If you want a result you do select * from functionname(). In plpgsql if you do not want a result you have to use the perform keyword as in: perform functionname(). The reason why it is hanging is because you have an infinite loop. loop fetch sys_cursor into loop_id; --you need to add this line Exit when not Found; array_append(result,loop_id); end loop; Sim ________________________________________________________________________________ Sim, Thanks for your help ! I changed the array_append(array,val) into select array_append(array,val), when I ran the function it says SampleDB=# select getmatch(array[2]); ERROR: SELECT query has no destination for result data HINT: If you want to discard the results, use PERFORM instead. tried this , select array_append(temp,loop_id) into result; and a call to function as select * from getmatch([2]); the function call is just hanging and not returning . after I press cntrl+c . the error is SampleDB=# select * from getmatch(array[2]); Cancel request sent ERROR: canceling query due to user request CONTEXT: SQL statement "SELECT array_append( $1 , $2 )" PL/pgSQL function "getmatch" line 9 at select into variables any help how to resolve this , like what mistake am I doing ? Thanks Vidya Sim Zacks <sim@xxxxxxxxxxxxxx> wrote: Vidya, Array_append is a function and is called - select array_append(array,val). You left out the select. Sim "Vidya" <sivaramanvidhya@xxxxxxxxx> wrote in message news:20050509122049.62500.qmail@xxxxxxxxxxxxxxxxxxxxxxxxxx Hi I have the following functions , the functions are created but when it is invoked it errors out / SampleDB=# create or replace function getmatch(anyarray) returns anyarray as $$ SampleDB$# declare SampleDB$# sys_cursor cursor(key integer) is select sys_id from subsystems wher sys_id = key; SampleDB$# result integer[]; SampleDB$# loop_id integer; SampleDB$# begin SampleDB$# open sys_cursor($1[1]); SampleDB$# loop SampleDB$# fetch sys_cursor into loop_id; SampleDB$# array_append(result,loop_id); SampleDB$# end loop; SampleDB$# close sys_cursor; SampleDB$# return result[]; SampleDB$# end; SampleDB$# $$ language plpgsql; CREATE FUNCTION SampleDB=# select getmatch(array[2]); ERROR: syntax error at or near "array_append" at character 1 QUERY: array_append( $1 , $2 ) CONTEXT: PL/pgSQL functio n "getmatch" line 9 at SQL statement LINE 1: array_append( $1 , $2 ) and the second function is similar SampleDB=# create or replace function getmatch() returns setof integer as $$ SampleDB$# declare SampleDB$# sys_cursor cursor is select sys_id from subsystems; SampleDB$# loop_id subsystems.sys_id%type; SampleDB$# begin SampleDB$# open sys_cursor; SampleDB$# loop SampleDB$# fetch sys_cursor into loop_id; SampleDB$# return next loop_id; SampleDB$# end loop; SampleDB$# close sys_cursor; SampleDB$# return; SampleDB$# end; SampleDB$# $$ language plpgsql; CREATE FUNCTION SampleDB=# select getmatch(); ERROR: set-valued function called in context that cannot accept a set CONTEXT: PL/pgSQL function "getmatch" line 8 at return next what is wrong in my function , anyhelp asap? Thanks Vidya __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
