Message-ID: <3DD11240.F355F946@rodos.fzk.de>
Date: Tue, 12 Nov 2002 15:37:52 +0100
From: Christoph Haller <ch@rodos.fzk.de>
MIME-Version: 1.0
To: karthickv@hclp.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Output of function into a file
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit

>
> 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