postgresql - Read data from a text file inside a trigger

Question

Is there any way by which data on an text file can be accessed inside a trigger function?

Answer 1

You can use standard plpgsql functions for this.

COPY

If we are talking about CSV files (or similar) and the file is on the db server you use COPY. I quote the manual here.

COPY with a file name instructs the PostgreSQL server to directly read from or write to a file. The file must be accessible to the server and the name must be specified from the viewpoint of the server.

For more complex operations you might want to use a temporary table as staging table, COPY the file into it and work with plain SQL commands from there. Similar to this example. Be sure you don't run into conflicts with trying to create the same table in the same session multiple times, though ...

pg_read_file()

There are also generic file access functions. For security reasons their use is rather restricted:

Only files within the database cluster directory and the log_directory can be accessed. Use a relative path for files in the cluster directory, and a path matching the log_directory configuration setting for log files. Use of these functions is restricted to superusers.

I use this to read in small XML files and process inside PostgreSQL. Demo:

CREATE OR REPLACE FUNCTION f_import_from_file()
  RETURNS boolean AS
$BODY$
DECLARE
   myxml    xml;
   datafile text := 'path/relative/to/db_cluster/myfile.xml';
BEGIN

myxml := pg_read_file(datafile, 0, 10000000);   -- 10 MB max.

-- do stuff, like INSERT ...

$BODY$

You can overcome the path restriction with a symlink from your db directory to any other directory. Be wary of possible security implications, though.

file_fdw

Finally, you could use a foreign data wrapper to access data files in the server's file system. You need the additional module file_fdw for this. Install once per database with:

CREATE EXTENSION file_fdw;