Re: function call error

[Vidya <sivaramanvidhya@xxxxxxxxx>]

Sim,

I have given a 'when not found raise exception '

yeah it says no matching records

but the records are there which matches 2 and 3

SampleDB=# select sys_id from subsystems where sys_id = 2 or sys_id =3;
 sys_id
--------
      2
      3
      2
      3
(4 rows)

 

there is a probs with array indexing ,am calling the function like

select * from getmatch(array[2,3]);

with this values am opening the cursor ,

like

SampleDB$# sys_cursor cursor(key integer) is select sys_id from subsystems where
 sys_id = key;

and opening the cursor with

SampleDB$# open sys_cursor($1[1]);

but this $1[1] is not referring to 2 otherwise the cursor would have fetched the correct values matching 2 .

think there is some type case which needs to be done to convert array type to integer .

 

Thanks for your inputs .

 

Vidya

Sim Zacks <sim@xxxxxxxxxxxxxx> wrote:

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 appendin g 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 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;
CREAT E 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

---

Yahoo! Mail
 
Stay connected, organized, and protected. Take the tour