Thread: 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/
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
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
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
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
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
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 > >