Thread: Non-superuser creating a flat file

Non-superuser creating a flat file

From
"Terri Reid"
Date:

I have data that is being updated in a table that I need to export to a flat file via a database trigger on insert or update. The user performing the update will not be a superuser. I’ve tried to use COPY TO, but that doesn’t work for non-superusers. Is there some other functionality that can write out flat files? I’m looking for something similar to the Oracle UTL file functionality, as ideally I’d want to append the data to an existing file and be able to manipulate the data before I wrote it out.

 

Thanks

Terri

 

Terri Reid

BCT Software Solutions

Mail: treid@edp.co.uk

Tel: 01925 732359

 

Re: Non-superuser creating a flat file

From
John DeSoi
Date:
On Aug 13, 2007, at 10:07 AM, Terri Reid wrote:

> I have data that is being updated in a table that I need to export
> to a flat file via a database trigger on insert or update. The user
> performing the update will not be a superuser. I’ve tried to use
> COPY TO, but that doesn’t work for non-superusers. Is there some
> other functionality that can write out flat files? I’m looking for
> something similar to the Oracle UTL file functionality, as ideally
> I’d want to append the data to an existing file and be able to
> manipulate the data before I wrote it out.

Maybe it could work if you create your function using the SECURITY
DEFINER option?

http://www.postgresql.org/docs/8.2/interactive/sql-createfunction.html



John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL