Re: function call error

[Vidya <sivaramanvidhya@xxxxxxxxx>]

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(arra y,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 "getma tch" 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! Mai l 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!