Thread: How to fix set-valued function called in context that cannot accept a set in earlier versions
How to fix set-valued function called in context that cannot accept a set in earlier versions
From
"Andrus"
Date:
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)
Re: How to fix set-valued function called in context that cannot accept a set in earlier versions
From
hubert depesz lubaczewski
Date:
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
Re: How to fix set-valued function called in context that cannot accept a set in earlier versions
From
Kenichiro Tanaka
Date:
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
================================================
Re: How to fix set-valued function called in context that cannot accept a set in earlier versions
From
Merlin Moncure
Date:
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
Re: How to fix set-valued function called in context that cannot accept a set in earlier versions
From
"Andrus"
Date:
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)
Re: How to fix set-valued function called in context that cannot accept a set in earlier versions
From
hubert depesz lubaczewski
Date:
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
Re: How to fix set-valued function called in contextthat cannot accept a set in earlier versions
From
"Andrus"
Date:
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)
Re: How to fix set-valued function called in context that cannot accept a set in earlier versions
From
"Andrus"
Date:
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.
Re: How to fix set-valued function called in contextthat cannot accept a set in earlier versions
From
Alban Hertroys
Date:
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!