Stored Procedure Error

Поиск
Список
Период
Сортировка
Искать

Stored Procedure Error

От:
"Derek Hamilton" <derek@capweb.com>
Дата:
Hello,

I'm getting an error attempting to run the code below.  The funny thing is,
this code worked a few hours ago.

The error is:
WARNING: line 53 at return next
ERROR: Set-valued function called in context that cannot accept a set

I'm not really sure what else might be needed to debug this code.  Any help
is much appreciated.


Here is the code:

create or replace function testFunction(integer) returns setof namefilings
as '
declare
 searchid ALIAS FOR $1;
 r names%rowtype;
 r2 namefilings%rowtype;
 select_text VARCHAR;
 get_nameid VARCHAR;

BEGIN
 get_nameid := ''select nameid from names join searchvalues
   on (name = LOWER(value)) where searchid = '' || searchid;

 select_text := ''select b.* from names as a join namefilings as b
   on (a.nameid = b.nameid)
   where a.nameid IN ('';

 FOR r IN EXECUTE get_nameid LOOP
  select_text := select_text || r.nameid || '','';
 end loop;
 select_text := RTRIM(select_text, '','');
 select_text := select_text || '') limit 10'';

 FOR r2 IN EXECUTE select_text LOOP
  return next r2;
 END LOOP;
 return;
END;
' language 'plpgsql';


Thanks,
Derek

Re: Stored Procedure Error

От:
Joe Conway <mail@joeconway.com>
Дата:
Derek Hamilton wrote:
> I'm getting an error attempting to run the code below.  The funny thing is,
> this code worked a few hours ago.
> 
> The error is:
> WARNING: line 53 at return next
> ERROR: Set-valued function called in context that cannot accept a set
> 

You haven't shown us the statement causing the error, but I'd bet you 
need to do:
   select * from testFunction(1);
instead of:
   select testFunction(1);

HTH,

Joe

Re: Stored Procedure Error

От:
Joe Conway <mail@joeconway.com>
Дата:
Rich Burgi wrote:
> unsubscribe
> 
> GET ME OFF THIS DAMN LIST
> 

Sorry -- not much I can do for you, but see below:

>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>>

FAQ