Обсуждение: function call error
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
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
Vidya,
Array_append is a function and is called - select array_append(array,val).
You left out the select.
Sim
"Vidya" <sivaramanvidhya@yahoo.com> wrote in message news:20050509122049.62500.qmail@web54301.mail.yahoo.com...HiI 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 similarSampleDB=# 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 nextwhat is wrong in my function ,anyhelp asap?ThanksVidya__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
For the second function you called select getmatch without specifying the columns you want.
try select * from getmatch()
"Vidya" <sivaramanvidhya@yahoo.com> wrote in message news:20050509122049.62500.qmail@web54301.mail.yahoo.com...HiI 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 similarSampleDB=# 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 nextwhat is wrong in my function ,anyhelp asap?ThanksVidya__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
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
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
"Vidya" <sivaramanvidhya@yahoo.com> wrote in message news:20050509122049.62500.qmail@web54301.mail.yahoo.com...HiI 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 similarSampleDB=# 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 nextwhat is wrong in my function ,anyhelp asap?ThanksVidya__________________________________________________
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
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@compulab.co.il> wrote: Vidya, Array_append is a function and is called - select array_append(array,val). You left out the select. Sim "Vidya" <sivaramanvidhya@yahoo.com> wrote in message news:20050509122049.62500.qmail@web54301.mail.yahoo.com... 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
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
----------
getmatch
----------
(1 row)
SampleDB=# select getmatch(array[2]);
getmatch
----------
getmatch
----------
(1 row)
SampleDB=# select getmatch(array[3]);
getmatch
----------
getmatch
----------
(1 row)
Thanks
Vidya
Sim Zacks <sim@compulab.co.il> 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@web54301.mail.yahoo.com...
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!
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 anarray 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 arrayand 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@compulab.co.il> 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@web54301.mail.yahoo.com... 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!
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;
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@compulab.co.il> 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@web54301.mail.yahoo.com...
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