Thread: can we write to a flat file from Postgresql procedure

can we write to a flat file from Postgresql procedure

From
R Vijayanath
Date:
Hi,

I found your name in the Postgresql web site.

It would be great if you can tell me if I can write a
procedure that can write the output to the OS(Linux
OS) file.

Can you assist me on this if there is a way to do it.

We are using Postgresql 7.1 running in Linux Redhat
7.1.

Thanks in Advance.

Vijay


__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail
http://personal.mail.yahoo.com/


Re: can we write to a flat file from Postgresql procedure

From
Jan Wieck
Date:
R Vijayanath wrote:
> Hi,
>
> I found your name in the Postgresql web site.
>
> It would be great if you can tell me if I can write a
> procedure that can write the output to the OS(Linux
> OS) file.
>
> Can you assist me on this if there is a way to do it.
>
> We are using Postgresql 7.1 running in Linux Redhat
> 7.1.
   In  version  7.1  is  a  procedural language PL/TclU. It's an   unrestricted (and thus untrusted) version of PL/Tcl.
Meaning,  since  it's  possible  to do any sort of OS level access from   within the backend under  the  postgres  UNIX
user-ID,  only   Postgres superusers can create functions in that language.
 
   That's  the  only  choice other than C you have. There are no   plans yet to modify PL/pgSQL in that direction.

>
> Thanks in Advance.
   You're welcome.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com



Re: can we write to a flat file from Postgresql procedure

From
Peter Eisentraut
Date:
R Vijayanath writes:

> It would be great if you can tell me if I can write a
> procedure that can write the output to the OS(Linux
> OS) file.

You could try out PL/sh for that.

http://www.postgresql.org/~petere/plsh.html

YMMV

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter



Re: can we write to a flat file from Postgresql procedure

From
Alex Pilosov
Date:
On Wed, 11 Jul 2001, Peter Eisentraut wrote:

> R Vijayanath writes:
> 
> > It would be great if you can tell me if I can write a
> > procedure that can write the output to the OS(Linux
> > OS) file.
> 
> You could try out PL/sh for that.
> 
> http://www.postgresql.org/~petere/plsh.html
Well, as long as we all keep plugging our favorite languages, in 7.2, you
can use pl/perlu ;)
-alex



RE: can we write to a flat file from Postgresql procedure

From
Jeff Eckermann
Date:
I don't think there is any way to do this using any of the generally
available tools.

You could do a workaround using pl/pgsql, in the following way:
*    insert your data into a temp table
*    output the data to a file using COPY

pl/pgsql allows INSERT statements, but does not allow CREATE/DROP TABLE or
COPY statements.  Those can be constructed using EXECUTE, which takes a
string (which can be constructed using parameters) and passes it on to be
executed as an SQL statement.

For more information, check out the PostgreSQL documentation, especially on
pl/pgsql (under "Procedural Languages") and COPY (under "SQL Commands").

> -----Original Message-----
> From:    R Vijayanath [SMTP:vijayanath@yahoo.com]
> Sent:    Thursday, July 05, 2001 10:34 AM
> To:    pgsql-sql@postgresql.org
> Subject:    can we write to a flat file from Postgresql procedure
> 
> Hi,
> 
> I found your name in the Postgresql web site.
> 
> It would be great if you can tell me if I can write a
> procedure that can write the output to the OS(Linux
> OS) file.
> 
> Can you assist me on this if there is a way to do it.
> 
> We are using Postgresql 7.1 running in Linux Redhat
> 7.1.
> 
> Thanks in Advance.
> 
> Vijay
> 
> 
> __________________________________________________
> Do You Yahoo!?
> Get personalized email addresses from Yahoo! Mail
> http://personal.mail.yahoo.com/
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly


RE: can we write to a flat file from Postgresql procedure

From
Jeff Eckermann
Date:
I haven't seen anything on this list to say that pl/perlu is being worked
on.  Is it?

> -----Original Message-----
> From:    Alex Pilosov [SMTP:alex@pilosoft.com]
> Sent:    Wednesday, July 11, 2001 10:03 AM
> To:    Peter Eisentraut
> Cc:    R Vijayanath; pgsql-sql@postgresql.org
> Subject:    Re: can we write to a flat file from Postgresql procedure
> 
> On Wed, 11 Jul 2001, Peter Eisentraut wrote:
> 
> > R Vijayanath writes:
> > 
> > > It would be great if you can tell me if I can write a
> > > procedure that can write the output to the OS(Linux
> > > OS) file.
> > 
> > You could try out PL/sh for that.
> > 
> > http://www.postgresql.org/~petere/plsh.html
> Well, as long as we all keep plugging our favorite languages, in 7.2, you
> can use pl/perlu ;)
> -alex
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly


RE: can we write to a flat file from Postgresql procedure

From
Alex Pilosov
Date:
Its done and committed to CVS, will be in 7.2 release.

Also done is DBD::PgSPI, interface to access postgres from within your
stored procedure. (www.formenos.org/PgSPI)

-alex

On Wed, 11 Jul 2001, Jeff Eckermann wrote:

> I haven't seen anything on this list to say that pl/perlu is being worked
> on.  Is it?
> 
> > -----Original Message-----
> > From:    Alex Pilosov [SMTP:alex@pilosoft.com]
> > Sent:    Wednesday, July 11, 2001 10:03 AM
> > To:    Peter Eisentraut
> > Cc:    R Vijayanath; pgsql-sql@postgresql.org
> > Subject:    Re: can we write to a flat file from Postgresql procedure
> > 
> > On Wed, 11 Jul 2001, Peter Eisentraut wrote:
> > 
> > > R Vijayanath writes:
> > > 
> > > > It would be great if you can tell me if I can write a
> > > > procedure that can write the output to the OS(Linux
> > > > OS) file.
> > > 
> > > You could try out PL/sh for that.
> > > 
> > > http://www.postgresql.org/~petere/plsh.html
> > Well, as long as we all keep plugging our favorite languages, in 7.2, you
> > can use pl/perlu ;)
> > -alex
> > 
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
> 
>