Thread: file I/O in plpgsql

I'm trying to write out query results to a text file from within a plpgsql
function, but having trouble. Can anyone tell me if this is possible? I'm
trying to perform \o filename, then select, but it squawks about no
destination for the select results. Any pointers?
-Meghan

Jessica M Salmon <jmsalmon@fs.fed.us> schrieb:

>
> I'm trying to write out query results to a text file from within a plpgsql
> function, but having trouble. Can anyone tell me if this is possible? I'm

IIRC no, you don't have access to the file system. PLPGSQL is a
*trusted* language. You have access to the file system with untrusted
languages like plpgperlu.


> trying to perform \o filename, then select, but it squawks about no

\o is a psql-client - feature.


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

On Friday 28 April 2006 03:47 pm, Jessica M Salmon <jmsalmon@fs.fed.us> thus
communicated:
-->
--> I'm trying to write out query results to a text file from within a
 plpgsql --> function, but having trouble. Can anyone tell me if this is
 possible? I'm --> trying to perform \o filename, then select, but it squawks
 about no --> destination for the select results. Any pointers?
--> -Meghan
-->
-->
--> ---------------------------(end of broadcast)---------------------------
--> TIP 2: Don't 'kill -9' the postmaster
-->
You cannot do that with plpgsql. You can use Perl in "trusted" mode and pass
the query results to the Perl function which can write the output to a ffile.

Jessica M Salmon wrote:
> I'm trying to write out query results to a text file from within a plpgsql
> function, but having trouble. Can anyone tell me if this is possible? I'm
> trying to perform \o filename, then select, but it squawks about no
> destination for the select results. Any pointers?
This is a really bad idea. PL/pgSql functions are called from inside
transactions, which might be rolled back at a later point. Rolling back
a transaction is basically "do as if the transaction had never existed",
so any changes your function does must be rolled back too. For database
inserts and updates postgresql takes care of that for you, but it can't
to that for files you might write.

For that reason, PL/pgSQl doesn't provide any means to access the
"outside world". Ülperl, plpyhton, plruby, pltcl, pljava, ... might
support writing files, but that doesn't make this any less dangerous..

The standard solution for things like that is to create a table that
queues any action you might want to trigger, (you can fill that queue
safely from a plpgsql function). Then you create a daemon or periodic
cron-job that scans the queue, and performs any necessary action.

greetings, Florian Pflug

On Saturday 29 April 2006 10:23 am, Terry Lee Tucker <terry@esc1.com> thus
communicated:
--> You cannot do that with plpgsql. You can use Perl in "trusted" mode and
 pass --> the query results to the Perl function which can write the output
 to a ffile. -->

Correcting typeo: s/trusted/untrusted/

Thanks everyone for their input on this.
-Meghan