Updated on 2024-05-20 GMT+08:00

file_fdw

The file_fdw module provides the foreign data wrapper file_fdw, which can be used to access data files in the file system of a server. The data file must be readable by COPY FROM. For details, see "SQL Reference > SQL Syntax > COPY" in Developer Guide. file_fdw is only used to access readable data files, but cannot write data to the data files.

By default, the file_fdw is compiled in GaussDB. During database initialization, the plug-in is created in the pg_catalog schema.

The server and foreign table corresponding to file_fdw can be created only by the initial user of the database or the O&M administrator who enables the O&M mode.

When you create a foreign table using file_fdw, you can add the following options:

  • filename

    File to be read. This parameter is required and must be an absolute path.

  • format

    File format of the remote server, which is the same as the FORMAT option in the COPY statement. The value can be text, csv, or binary.

  • header

    Specifies whether a specified file has a header, which is the same as the HEADER option of the COPY statement.

  • delimiter

    File delimiter, which is the same as the DELIMITER option of the COPY statement.

  • quote

    Quote character of a file, which is the same as the QUOTE option of the COPY statement.

  • escape

    Escape character of a file, which is the same as the ESCAPE option of the COPY statement.

  • null

    Null string of a file, which is the same as the NULL option of the COPY statement.

  • encoding

    Encoding of a file, which is the same as the ENCODING option of the COPY statement.

  • force_not_null

    File-level null option, which is a Boolean option. If it is true, the value of the declared field cannot be an empty string. This option is the same as the FORCE_NOT_NULL option of the COPY statement.

  • file_fdw does not support the OIDS and FORCE_QUOTE options of the COPY statement.
  • These options can only be declared for a foreign table or the columns of the foreign table, not for the file_fdw itself, nor for the server or user mapping that uses file_fdw.
  • To modify table-level options, you must obtain the system administrator role permissions. For security reasons, only the system administrator can determine the files to be read.
  • For a foreign table that uses file_fdw, running EXPLAIN displays the name and size (in bytes) of the file to be read. If the keyword COSTS OFF is specified, the file size is not displayed.

Using file_fdw

  • To create a server object, run CREATE SERVER.
  • To create a user mapping, run CREATE USER MAPPING.
  • To drop a user mapping, run DROP USER MAPPING.
  • To drop a server object, run DROP SERVER.

Precautions

  • To use file_fdw, you need to specify the file to be read. Prepare the file and grant the read permission on the file for the database to access the file.
  • DROP EXTENSION file_fdw is not supported.
  • The extended function is for internal use only. You are advised not to use it.