CODEWITHSUNDEEP
mysqlpostgresqlfile-io
Damiano Barbati
Damiano Barbati

Reputation: 3496

PostgreSQL: load a file from filesystem without inserting into a table

How do I do such a thing?
In mysql I do:

SELECT LOAD_FILE('/path/to/file');

What about postgres? Without using the \copy command of psql?

Upvotes: 0

Views: 3241

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657777

That depends what you want to do exactly.

  • You have COPY for reading structured data into (temporary) tables.
    Note that this is the SQL command, which is similar, but not the same as the \copy command of psql!

  • And there is pg_read_file() for reading in any text file.

Edit - a basic example:

CREATE FUNCTION f_showfile(myfile text)
  RETURNS text AS
$x$
BEGIN

RETURN pg_read_file(myfile, 0, 1000000); -- 1 MB max.
-- or you could read into a text var and do stuff with it.

END;
$x$
  LANGUAGE plpgsql VOLATILE;

Only superusers can use this function. Be careful not to open security holes. You could create a function with SECURITY DEFINER, REVOKE FROMpublic and GRANT TO selected roles. If security is an issue read this paragraph at the provided link:

Writing SECURITY DEFINER Functions Safely

pg_read_file() you can only read from the logfile dir and the database dir. On Linux you could create a symlink to a data dir (at a safe location) like this:

cd /path//my/database
ln -s /var/lib/postgresql/text_dir/ .

Then call like this:

SELECT f_showfile('text_dir/readme.txt');

Output:

                                            f_showfile
-------------------------------------------------------------------------
 This is my text from a file.

Upvotes: 6

Related Questions