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
