Thread: Embedded C function returning a set of rows

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.
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);"

I know that this seems stupid, but please answer me only if you know how
to do this...

Thanks in advance.

--
Alessandro Candini

MEEO S.r.l.
Via Saragat 9
I-44122 Ferrara, Italy
Tel: +39 0532 1861501
Fax: +39 0532 1861637
http://www.meeo.it

========================================
"ATTENZIONE:le informazioni contenute in questo messaggio sono
da considerarsi confidenziali ed il loro utilizzo è riservato unicamente
al destinatario sopra indicato. Chi dovesse ricevere questo messaggio
per errore è tenuto ad informare il mittente ed a rimuoverlo
definitivamente da ogni supporto elettronico o cartaceo."

"WARNING:This message contains confidential and/or proprietary
information which may be subject to privilege or immunity and which
is intended for use of its addressee only. Should you receive this
message in error, you are kindly requested to inform the sender and
to definitively remove it from any paper or electronic format."

On Mon, Feb 14, 2011 at 7:41 AM, Alessandro Candini <candini@meeo.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