I would guess that the cursor is not finding any records. use notify in the code to see what what values it is finding. see section 35.9 in the help for how to do this. put a notify statement on each line that assigns a relevant value and see what it is. You can also do a notify on the array to see if there is anything in it. That's the best way I have found to do debugging in a non IDE environment. Sim ________________________________________________________________________________ Sim, yeah , after inserting a exit when not found , it is not looping indefinitely, but the function is supposed to return an array as per the declaration , like I am calling the function with an array select * from getmatch(array[2]); and I am selecting the records into the cursor which matches this 2 and getting into loop_id then appending into an array and returning the array . But when I call the func , no value is displayed , it is supposed to display the result as [2,2] but it is not ? SampleDB=# select * from getmatch(array[2]); getmatch ---------- (1 row) SampleDB=# select getmatch(array[2]); getmatch ---------- (1 row) SampleDB=# select getmatch(array[3]); getmatch ---------- (1 row) Thanks Vidya Sim Zacks <sim@xxxxxxxxxxxxxx> wrote: 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 wrote: Vidya, Array_append is a function and is called - select array_append(array,val). You left out the select. Sim "Vidya" 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 --------------------------------- Discover Yahoo! Stay in touch with email, IM, photo sharing & more. Check it out! ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
