Re: Embedded C function returning a set of rows

On Mon, Feb 14, 2011 at 7:41 AM, Alessandro Candini <candini(at)meeo(dot)it> wrote:
> I have read http://www.postgresql.org/docs/9.0/static/xfunc-c.html, but it
> is not clear to me how to implement a function which retrieve a set of rows.
> In the example given it seems that I have to recall my function for every
> row, but this is not what I want.

It will call your function for every row. That's why you have macros
that tell you if it's the first invocation where you typically do some
setup work. There are also helper structures to manage context. Head
over to http://doxygen.postgresql.org/ and search on a function like
'generate_series' and this you will get simple examples of C functions
returning data.

> With libpq I can perform a query which returns a set of rows in a single
> operation.

> What types and methos have I to use in order to output my result, embedding
> my function in postgresql?
>
> Can anyone give me a simple example?
>
> I want to do something like this:
> psql -p 5433 -d mydb -U myuser -C "SELECT myfunc(SELECT * FROM mytable);"

You can't do this exactly because you can't direct the output of a
query (a set) to a function. A function input argument must be a
specific type or an array of a type. You can however do something
like this:

SELECT myfunc(array(SELECT m FROM mytable m));
with myfunc defined as:
create function myfunc(mytables mytable[]) returns something as...

which will give you an array of type 'mytable'. note this approach is
suitable for small sets (say 10k records or less). after that you
should look at breaking up the set and paging it into the function (or
do that in a wrapper function, probably in pl/pgsql). Also, this is
more a question of getting data *in* to a function, not out of it,
which are completely separate problems in terms of the backend API.

merlin