Search code examples
postgresqltexttriggers

Read data from a text file inside a trigger

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

Solution

  • You can use standard PL/pgSQL functions.

    COPY

    For CSV files (or similar) on the db server use COPY. The manual.

    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 ...

    If the file is local to your client and/or you have restricted privileges, consider \copy in psql instead. See:

    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
  LANGUAGE plpgsql AS
$func$
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 ...

$func$

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

    file_fdw

    You can also 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;