Re: function call error

From: "Sim Zacks" <sim(at)compulab(dot)co(dot)il>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: function call error
Date: 2005-05-09 14:38:42
Message-ID: d5np90$jvt$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

For the second function you called select getmatch without specifying the columns you want.
try select * from getmatch()
"Vidya" <sivaramanvidhya(at)yahoo(dot)com> wrote in message news:20050509122049(dot)62500(dot)qmail(at)web54301(dot)mail(dot)yahoo(dot)com(dot)(dot)(dot)
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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Abdul-Wahid Paterson 2005-05-09 14:39:57 need trigger help
Previous Message Sim Zacks 2005-05-09 14:36:47 Re: function call error