Skip site navigation (1) Skip section navigation (2)

Re: file I/O in plpgsql

From: "Florian G(dot) Pflug" <fgp(at)phlo(dot)org>
To: Jessica M Salmon <jmsalmon(at)fs(dot)fed(dot)us>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: file I/O in plpgsql
Date: 2006-04-29 14:25:58
Message-ID: 44537776.1060809@phlo.org (view raw or flat)
Thread:
Lists: pgsql-general
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


In response to

pgsql-general by date

Next:From: Terry Lee TuckerDate: 2006-04-29 14:28:42
Subject: Re: file I/O in plpgsql
Previous:From: Terry Lee TuckerDate: 2006-04-29 14:23:54
Subject: Re: file I/O in plpgsql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group