Re: function call error - Mailing list pgsql-general

From Vidya
Subject Re: function call error
Date
Msg-id 20050510072356.77669.qmail@web54302.mail.yahoo.com
Whole thread Raw
In response to Re: function call error  ("Sim Zacks" <sim@compulab.co.il>)
Responses Re: function call error
List pgsql-general
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@compulab.co.il> wrote:
Vidya,
 
Array_append is a function and is called - select array_append(array,val).
You left out the select.
 
Sim
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

pgsql-general by date:

Previous
From: Jerome Macaranas
Date:
Subject: Re: Need input on postgres used for phpBB - addtional note
Next
From: Sim Zacks
Date:
Subject: Re: function call error