table functions as fields in a select

Started by Max Jacobover 21 years ago3 messagesgeneral
Jump to latest
#1Max Jacob
Max.Jacob@ircam.fr

hallo,
i am using pg7.3.4. i have written a table function in c that tokenizes
a text, returning a setof text. i can use this function either in
constructs like:
select * from tokenize( 'some text that has to be tokenized' );
or in costructs like:
select tokenize( text_field ) from some_table;
the latter gives me the whole result of all words contained in every
"text_field" of "some_table". i wonder if this is a forseen behaviour,
since doing the same with a plpgsql function does not work (the plpgsql
interpreter raises an error when it reaches the "return next" statement
telling i'm using a "set-valued function in a context that can not
accept a set"). but the c function works fine. now i'm trying the 8beta,
where my "tokenize" function does not work anymore in the second
construct. the server does not alert me, it simply returns an empy
record set. this behaviour for sure not the right one since it should
raise an error, but mainly i would really like it to work at least as it
did on 7.3.4 since, as i believe my little example shows, this can be
very useful. does anybody know what is the forseen behavior for using a
set returning function in the field list of a select and, if the answer
is "it should raise an error", if it is possible to work around it?

thanks in advance,

max.

ps: i did not subscribe the mailing list, so please use "replay all"..

#2Max Jacob
Max.Jacob@ircam.fr
In reply to: Max Jacob (#1)
Re: table functions as fields in a select

sorry, forget this: it was my fault, now the function works also on pg8.
anyway, it is strange that i can do this with c functions and not with
plpgsql ones...
m.

max jacob wrote:

Show quoted text

hallo,
i am using pg7.3.4. i have written a table function in c that
tokenizes a text, returning a setof text. i can use this function
either in constructs like:
select * from tokenize( 'some text that has to be tokenized' );
or in costructs like:
select tokenize( text_field ) from some_table;
the latter gives me the whole result of all words contained in every
"text_field" of "some_table". i wonder if this is a forseen behaviour,
since doing the same with a plpgsql function does not work (the
plpgsql interpreter raises an error when it reaches the "return next"
statement telling i'm using a "set-valued function in a context that
can not accept a set"). but the c function works fine. now i'm trying
the 8beta, where my "tokenize" function does not work anymore in the
second construct. the server does not alert me, it simply returns an
empy record set. this behaviour for sure not the right one since it
should raise an error, but mainly i would really like it to work at
least as it did on 7.3.4 since, as i believe my little example shows,
this can be very useful. does anybody know what is the forseen
behavior for using a set returning function in the field list of a
select and, if the answer is "it should raise an error", if it is
possible to work around it?

thanks in advance,

max.

ps: i did not subscribe the mailing list, so please use "replay all"..

#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: Max Jacob (#1)
Re: table functions as fields in a select

max jacob <Max.Jacob@ircam.fr> writes:

does anybody know what is the forseen behavior for using a
set returning function in the field list of a select

It's deprecated, but will work if the function uses the SFRM_ValuePerCall
API (which plpgsql does not).

regards, tom lane