Thread: create a text file from postgres (like Oracle UTL_FILE package)
Hi, Anybody know/have functions or package to create text file from postgres function? I want to execute a function to take some fields from a table and save it in a text file (or xml format). I need function like: createfile(filename); appendtofile(filename,text); etc. thanks __________________________________ Do you Yahoo!? Check out the new Yahoo! Front Page. www.yahoo.com
It seems like you would find it much easier to do this with a language external to postgres (E.g., Perl) than relying on any of the built-in languages and file I/O mechanisms. Is there a reason you need postgres to handle this functionality? -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Nov 4, 2004, at 9:03 AM, Leo Martin Orfei wrote: > Hi, > > Anybody know/have functions or package to create text > file from postgres function? > > I want to execute a function to take some fields from > a table and save it in a text file (or xml format). > I need function like: > createfile(filename); > appendtofile(filename,text); > etc. > > thanks
At this point, I'm not sure I'm understanding your question. I'll try restating my solution again: I would have, for example, a Perl script that used the DBI module to extract information from a given database and then let Perl handle any relevant text/data manipulation and file creation/manipulation. DBI should allow the execution of arbitrary SQL against any supported database, and postgres is supported. Also, I'd recommend continuing to post and not merely emailing me directly to keep more eyes on your problem. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Nov 5, 2004, at 12:20 PM, Leo Martin Orfei wrote: > If I let the script connect to DB and create files (I > belive in this solution), how I execute the script > from a client side? may be a function? some OS system > call? > > thanks. > > --- "Thomas F. O'Connell" <tfo@sitening.com> wrote: > >> You're thinking about it in reverse. Let the script >> make calls via a >> database interface. There are database interfaces to >> postgres in a >> variety of modern programming languages. >> >> -tfo >> >> -- >> Thomas F. O'Connell >> Co-Founder, Information Architect >> Sitening, LLC >> http://www.sitening.com/ >> 110 30th Avenue North, Suite 6 >> Nashville, TN 37203-6320 >> 615-260-0005 >> >> On Nov 5, 2004, at 7:24 AM, Leo Martin Orfei wrote: >> >>> ok, this appears to be a good solution... >>> >>> so, how I execute a perl (or C, or bash ) script >> from >>> a postgresql function? >>> >>> if I use C (I think that write C code and use it >> in >>> the database is possible), I have to connect to >> the >>> database from my C code or it's embebed and I can >> use >>> tables directly? >>> >>> thanks.
ok. I gonna explain a little more my situation.
I have a postgres server (Linux) and clients
(Win2k+delphi application).
I need create a text file with some columns from a
table in the server machine, but this file must be
created only when the user wants. (click button on my
delphi program on the client side create a text file
on the server file system).
I can't share o mount a resource or connect by socket.
I think execute a postgres function from delphi and
leave to postgres a work to create the file.
so, my problem is how to create a text file from a
postgres function. may be I can use a java program
(like Oracle Java Stored Procedures).
How I execute a external java program from postgres
function?
exist some OS system call in postgres?
something like
Create or Replace Function run () returns int4 as
begin
execute_OS_system_call('java -jar someapp.jar');
return (1);
end;
thanks
--- "Thomas F.O'Connell" <tfo@sitening.com> wrote:
> At this point, I'm not sure I'm understanding your
> question. I'll try
> restating my solution again:
>
> I would have, for example, a Perl script that used
> the DBI module to
> extract information from a given database and then
> let Perl handle any
> relevant text/data manipulation and file
> creation/manipulation.
>
> DBI should allow the execution of arbitrary SQL
> against any supported
> database, and postgres is supported.
>
> Also, I'd recommend continuing to post and not
> merely emailing me
> directly to keep more eyes on your problem.
>
> -tfo
>
> --
> Thomas F. O'Connell
> Co-Founder, Information Architect
> Sitening, LLC
> http://www.sitening.com/
> 110 30th Avenue North, Suite 6
> Nashville, TN 37203-6320
> 615-260-0005
>
> On Nov 5, 2004, at 12:20 PM, Leo Martin Orfei wrote:
>
> > If I let the script connect to DB and create files
> (I
> > belive in this solution), how I execute the
> script
> > from a client side? may be a function? some OS
> system
> > call?
> >
> > thanks.
> >
> > --- "Thomas F. O'Connell" <tfo@sitening.com>
> wrote:
> >
> >> You're thinking about it in reverse. Let the
> script
> >> make calls via a
> >> database interface. There are database interfaces
> to
> >> postgres in a
> >> variety of modern programming languages.
> >>
> >> -tfo
> >>
> >> --
> >> Thomas F. O'Connell
> >> Co-Founder, Information Architect
> >> Sitening, LLC
> >> http://www.sitening.com/
> >> 110 30th Avenue North, Suite 6
> >> Nashville, TN 37203-6320
> >> 615-260-0005
> >>
> >> On Nov 5, 2004, at 7:24 AM, Leo Martin Orfei
> wrote:
> >>
> >>> ok, this appears to be a good solution...
> >>>
> >>> so, how I execute a perl (or C, or bash ) script
> >> from
> >>> a postgresql function?
> >>>
> >>> if I use C (I think that write C code and use it
> >> in
> >>> the database is possible), I have to connect to
> >> the
> >>> database from my C code or it's embebed and I
> can
> >> use
> >>> tables directly?
> >>>
> >>> thanks.
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Hi,
Am Sa, den 06.11.2004 schrieb Leo Martin Orfei um 13:52:
> ok. I gonna explain a little more my situation.
>
> I have a postgres server (Linux) and clients
> (Win2k+delphi application).
> I need create a text file with some columns from a
> table in the server machine, but this file must be
> created only when the user wants. (click button on my
> delphi program on the client side create a text file
> on the server file system).
> I can't share o mount a resource or connect by socket.
> I think execute a postgres function from delphi and
> leave to postgres a work to create the file.
>
> so, my problem is how to create a text file from a
> postgres function. may be I can use a java program
> (like Oracle Java Stored Procedures).
> How I execute a external java program from postgres
> function?
> exist some OS system call in postgres?
>
> something like
> Create or Replace Function run () returns int4 as
> begin
> execute_OS_system_call('java -jar someapp.jar');
> return (1);
> end;
Well. Although there is a project for java as function
language, I dont think its the best option when you
look at startup times and memory footprint.
You can either do with temp tables and copy
or use any of the *u languages (u=unrestricted)
for example plpythonu
CREATE FUNCTION makefile(text) RETURNS text AS '
o=open("/path/to/file")
o.write(args[0])
o.close()
return "ok"
' LANGUAgE plpythonu;
Regards
Tino