Обсуждение: How to fix set-valued function called in context that cannot accept a set in earlier versions

In 8.3 code below causes exception

ERROR:  set-valued function called in context that cannot accept a set
CONTEXT:  PL/pgSQL function "wordwrap" line 21 at RETURN NEXT

How to fix this so that wordwrap works in any PostgreSql 8.x version or at
least in
8.3 and 8.4 ?

Andrus

CREATE OR REPLACE FUNCTION wordwrap(line text, linelen integer)
RETURNS SETOF text as $$
DECLARE
  words text[] := string_to_array(line,' ');
  i integer;
  res text:='';

BEGIN
  if trim(line)='' then
    return next '';
    return;
    end if;
 for i IN 1 .. array_upper(words,1) LOOP
   if length(res)+length(words[i]) > linelen THEN
     return next res;
     res := '';
     END IF ;
   if res<>'' then
     res := res || ' ';
     end if;
   res := res || words[i];
   end loop;
return next res;
END
 $$ LANGUAGE plpgsql;

select wordwrap('fdgdf',10)

On Mon, Apr 05, 2010 at 02:14:33PM +0300, Andrus wrote:
> select wordwrap('fdgdf',10)

select * from wordwrap('...', ...);

depesz

--
Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

Hello, Andrus

I tested this reproduce case.

8.3.9   return exception
8.3.10 return exception
8.4.0   Crash Server!!
8.4.1   OK
8.4.2   OK
8.4.3   OK

Just for your information,
8.4.0's clash made this call stack(I analyzed core file)

#0  DirectFunctionCall1 (func=0x8095a70 <hashoid>, arg1=16421) at
fmgr.c:1012
1012            result = (*func) (&fcinfo);
(gdb) where
#0  DirectFunctionCall1 (func=0x8095a70 <hashoid>, arg1=16421) at
fmgr.c:1012
#1  0x082c9eab in CatalogCacheComputeHashValue (cache=0xa091058,
     nkeys=<value optimized out>, cur_skey=0xbeca1298) at catcache.c:207
#2  0x082cad19 in SearchCatCache (cache=0xa091058, v1=16421, v2=0, v3=0,
v4=0)
     at catcache.c:1137
#3  0x082df1cf in fmgr_info_cxt_security (functionId=16421,
finfo=0xbeca144c,
     mcxt=<value optimized out>, ignore_security=0 '\0') at fmgr.c:209
#4  0x082df881 in fmgr_info (functionId=16421, finfo=0xbeca144c) at
fmgr.c:156
#5  0x082df282 in fmgr_info_cxt_security (functionId=43373,
finfo=0xb56c6320,
     mcxt=<value optimized out>, ignore_security=1 '\001') at fmgr.c:406
#6  0x082e0520 in fmgr_security_definer (fcinfo=0xa0e2f34) at fmgr.c:898
#7  0x082e041c in fmgr_security_definer (fcinfo=0xa0e2f34) at fmgr.c:961
#8  0x082e041c in fmgr_security_definer (fcinfo=0xa0e2f34) at fmgr.c:961

I usually see this call stack  in version 8.4.0 and I guess this error
does not occur in 8.4.1

Thank you.

(2010/04/05 20:14), Andrus wrote:
> In 8.3 code below causes exception
>
> ERROR:  set-valued function called in context that cannot accept a set
> CONTEXT:  PL/pgSQL function "wordwrap" line 21 at RETURN NEXT
>
> How to fix this so that wordwrap works in any PostgreSql 8.x version
> or at least in
> 8.3 and 8.4 ?
>
> Andrus
>
> CREATE OR REPLACE FUNCTION wordwrap(line text, linelen integer)
> RETURNS SETOF text as $$
> DECLARE
>  words text[] := string_to_array(line,' ');
>  i integer;
>  res text:='';
>
> BEGIN
>  if trim(line)='' then
>    return next '';
>    return;
>    end if;
> for i IN 1 .. array_upper(words,1) LOOP
>   if length(res)+length(words[i]) > linelen THEN
>     return next res;
>     res := '';
>     END IF ;
>   if res<>'' then
>     res := res || ' ';
>     end if;
>   res := res || words[i];
>   end loop;
> return next res;
> END
> $$ LANGUAGE plpgsql;
>
> select wordwrap('fdgdf',10)
>


--
================================================
Kenichiro Tanaka
K.K.Ashisuto
http://www.ashisuto.co.jp/english/index.html
================================================

On Mon, Apr 5, 2010 at 7:28 AM, hubert depesz lubaczewski
<depesz@depesz.com> wrote:
> On Mon, Apr 05, 2010 at 02:14:33PM +0300, Andrus wrote:
>> select wordwrap('fdgdf',10)
>
> select * from wordwrap('...', ...);

another workaround is to wrap the pl/pgsql function in sql function.
it's not always easy to re-work the function all into a 'from'
expression.

merlin

Thank you.

> another workaround is to wrap the pl/pgsql function in sql function.
> it's not always easy to re-work the function all into a 'from'
> expression.

I tried code below in 8.3 but got error

ERROR:  syntax error at or near "select"
LINE 30: select * from wordwrap(line,linelen);

Andrus.

CREATE OR REPLACE FUNCTION wordwrap(line text, linelen integer)
RETURNS SETOF text as $$
DECLARE
  words text[] := string_to_array(line,' ');
  i integer;
  res text:='';

BEGIN
  if trim(line)='' then
    return next '';
    return;
    end if;
 for i IN 1 .. array_upper(words,1) LOOP
   if length(res)+length(words[i]) > linelen THEN
     return next res;
     res := '';
     END IF ;
   if res<>'' then
     res := res || ' ';
     end if;
   res := res || words[i];
   end loop;
return next res;
END
 $$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION wordwrap83(line text, linelen integer)
RETURNS SETOF text as $$
BEGIN
select * from wordwrap(line,linelen);
END
 $$ LANGUAGE sql;


select wordwrap83('fdgdf',10)

On Mon, Apr 05, 2010 at 05:26:39PM +0300, Andrus wrote:
> Thank you.
>
>> another workaround is to wrap the pl/pgsql function in sql function.
>> it's not always easy to re-work the function all into a 'from'
>> expression.
>
> I tried code below in 8.3 but got error
> CREATE OR REPLACE FUNCTION wordwrap83(line text, linelen integer)
> RETURNS SETOF text as $$
> BEGIN
> select * from wordwrap(line,linelen);
> END
> $$ LANGUAGE sql;

remove begin and end from above function.

Best regards,

depesz

--
Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

Than you.

> remove begin and end from above function.

I tried code belwo in 8.3 but got error

ERROR:  column "line" does not exist
LINE 29: select * from wordwrap(line,linelen);

Andrus.

CREATE OR REPLACE FUNCTION wordwrap(line text, linelen integer)
RETURNS SETOF text as $$
DECLARE
  words text[] := string_to_array(line,' ');
  i integer;
  res text:='';

BEGIN
  if trim(line)='' then
    return next '';
    return;
    end if;
 for i IN 1 .. array_upper(words,1) LOOP
   if length(res)+length(words[i]) > linelen THEN
     return next res;
     res := '';
     END IF ;
   if res<>'' then
     res := res || ' ';
     end if;
   res := res || words[i];
   end loop;
return next res;
END
 $$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION wordwrap83(line text, linelen integer)
RETURNS SETOF text as $$
select * from wordwrap(line,linelen);
 $$ LANGUAGE sql;


select wordwrap83('fdgdf',10)

Osvaldo,

>CREATE OR REPLACE FUNCTION wordwrap83(line text, linelen integer)
>RETURNS SETOF text as $$
>select * from wordwrap($1,$2);
> $$ LANGUAGE sql;

Thank you. It works.

>select * FROM wordwrap83('fdgdf',10)
>If you have a "RETURN SETOF" function you must call it with "SELECT *
>FROM your_function();" not "SELECT your_function()"

select wordwrap83('fdgdf',10)

works in 8.3 and in 8.4

Andrus.

On 5 Apr 2010, at 16:33, Andrus wrote:

> Than you.
>
>> remove begin and end from above function.
>
> I tried code belwo in 8.3 but got error
> ERROR:  column "line" does not exist
> LINE 29: select * from wordwrap(line,linelen);
>
> Andrus.

Yes of course, you don't specify where to take line or linelen from. You probably meant to put some constant values
thereor results from another table. 

> select wordwrap83('fdgdf',10)


^^^^^^
These values for example.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4bba1f2b10411047147693!