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

Re: storing a text file

From: Jasen Betts <jasen(at)xnet(dot)co(dot)nz>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: storing a text file
Date: 2010-02-16 09:59:29
Message-ID: hldqa1$kjh$1@reversiblemaps.ath.cx (view raw or flat)
Thread:
Lists: pgsql-novice
On 2010-02-15, Mag Gam <magawake(at)gmail(dot)com> wrote:
> Thanks.
>
> I would like to load an entire text file into a field of a table.Is
> there an easy way to do this or do I have to write a script?

I was wondering about that yesterday...

the function pg_read_file seems to be one way to do it.
the function needs to be invoked by a database superuser 
(eg. postgres), and the file must be readable by the 
database system user account (eg. postgres)
and must be visible from a path that starts at your databases data
directory (you could create a symlink...)

it can be wrapped in a function that is owned by a database superuser
and has the "SECUITY DEFINER" attribute.

http://www.postgresql.org/docs/8.4/static/functions-admin.html#FUNCTIONS-ADMIN-GENFILE

lo_import (the sql function) is another way to read files and the content can be pulled
from its storage in pg_catalog.pg_largeobject using the right query
with an agregate function to join the parts. but using lo_import you still
have all the constraints of pg_read_file except the path constraint.

So in general "write a script" is actually the best solution, or build
it into some part of your application's UI.


In response to

pgsql-novice by date

Next:From: Machiel RichardsDate: 2010-02-17 10:58:33
Subject: check_postgres
Previous:From: Tom LaneDate: 2010-02-16 04:25:36
Subject: Re: Actions requiring commit

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