From: Christoph Haller <ch@rodos.fzk.de>
To: karthickv@hclp.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Output of function into a file
Date: Tue, 12 Nov 2002 15:37:52 +0100
Message-ID: <3DD11240.F355F946@rodos.fzk.de> (raw)
>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ch@rodos.fzk.de, karthickv@hclp.com
Subject: Re: Output of function into a file
In-Reply-To: <3DD11240.F355F946@rodos.fzk.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox