Re: storing a text file

On 2010-02-15, Mag Gam <magawake@gmail.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.