>
> I am writing a small function to manipulate records in multiple
> tables. Since the function cannot return more than one value, I would
> like to get all the outputs of the queries and append them into a text
> file.
>
> Is there any way to do it inside the function. I came across many
> postings which tell me how to use it in pgsql but not inside the
> function.
>
You did not tell which function language you're talking about.
I'm assuming it's plpgsql.
First, did you check the cursors section?
Rather than executing a whole query at once, it is possible to set up a
cursor that encapsulates the query, and then read the query
result a few rows at a time. One reason for doing this is to avoid
memory overrun when the result contains a large number of rows.
(However, PL/pgSQL users don't normally need to worry about that, since
FOR loops automatically use a cursor internally to avoid
memory problems.) A more interesting possibility is that a function can
return a reference to a cursor that it has set up, allowing
the caller to read the rows. This provides one way of returning a rowset
from a function.
Taken from "PostgreSQL 7.2.1 Documentation Chapter 23. PL/pgSQL - SQL
Procedural Language"
Second, if this is not what you want to use,
I have not seen anything within plpgsql which could be used
to write to a file. But, what about a database table to be used as a
file?
e.g.
CREATE TABLE file_replacement(one_line TEXT);
then populate it by inserts like
INSERT INTO file_replacement VALUES('<one-row-of-your-query-result>');
and to get them back in FIFO order
SELECT one_line FROM file_replacement ORDER BY oid;
Regards, Christoph
