create a text file from postgres (like Oracle UTL_FILE package)

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:

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:

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