</indexterm>
<para>
- The <filename>file_fdw</> module provides the foreign-data wrapper
+ The <filename>file_fdw</filename> module provides the foreign-data wrapper
<function>file_fdw</function>, which can be used to access data
- files in the server's file system. Data files must be in a format
+ files in the server's file system, or to execute programs on the server
+ and read their output. The data file or program output must be in a format
that can be read by <command>COPY FROM</command>;
- see <xref linkend="sql-copy"> for details.
- Access to such data files is currently read-only.
+ see <xref linkend="sql-copy"/> for details.
+ Access to data files is currently read-only.
</para>
<para>
<listitem>
<para>
- Specifies the file to be read. Required. Must be an absolute path name.
+ Specifies the file to be read. Must be an absolute path name.
+ Either <literal>filename</literal> or <literal>program</literal> must be
+ specified, but not both.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>program</literal></term>
+
+ <listitem>
+ <para>
+ Specifies the command to be executed. The standard output of this
+ command will be read as though <command>COPY FROM PROGRAM</command> were used.
+ Either <literal>program</literal> or <literal>filename</literal> must be
+ specified, but not both.
</para>
</listitem>
</varlistentry>
<listitem>
<para>
- Specifies the file's format,
- the same as <command>COPY</>'s <literal>FORMAT</literal> option.
+ Specifies the data format,
+ the same as <command>COPY</command>'s <literal>FORMAT</literal> option.
</para>
</listitem>
</varlistentry>
<listitem>
<para>
- Specifies whether the file has a header line,
- the same as <command>COPY</>'s <literal>HEADER</literal> option.
+ Specifies whether the data has a header line,
+ the same as <command>COPY</command>'s <literal>HEADER</literal> option.
</para>
</listitem>
</varlistentry>
<listitem>
<para>
- Specifies the file's delimiter character,
- the same as <command>COPY</>'s <literal>DELIMITER</literal> option.
+ Specifies the data delimiter character,
+ the same as <command>COPY</command>'s <literal>DELIMITER</literal> option.
</para>
</listitem>
</varlistentry>
<listitem>
<para>
- Specifies the file's quote character,
- the same as <command>COPY</>'s <literal>QUOTE</literal> option.
+ Specifies the data quote character,
+ the same as <command>COPY</command>'s <literal>QUOTE</literal> option.
</para>
</listitem>
</varlistentry>
<listitem>
<para>
- Specifies the file's escape character,
- the same as <command>COPY</>'s <literal>ESCAPE</literal> option.
+ Specifies the data escape character,
+ the same as <command>COPY</command>'s <literal>ESCAPE</literal> option.
</para>
</listitem>
</varlistentry>
<listitem>
<para>
- Specifies the file's null string,
- the same as <command>COPY</>'s <literal>NULL</literal> option.
+ Specifies the data null string,
+ the same as <command>COPY</command>'s <literal>NULL</literal> option.
</para>
</listitem>
</varlistentry>
<listitem>
<para>
- Specifies the file's encoding,
- the same as <command>COPY</>'s <literal>ENCODING</literal> option.
+ Specifies the data encoding,
+ the same as <command>COPY</command>'s <literal>ENCODING</literal> option.
</para>
</listitem>
</varlistentry>
</variablelist>
<para>
- Note that while <command>COPY</> allows options such as OIDS and HEADER
- to be specified without a corresponding value, the foreign data wrapper
- syntax requires a value to be present in all cases. To activate
- <command>COPY</> options normally supplied without a value, you can
- instead pass the value TRUE.
+ Note that while <command>COPY</command> allows options such as <literal>HEADER</literal>
+ to be specified without a corresponding value, the foreign table option
+ syntax requires a value to be present in all cases. To activate
+ <command>COPY</command> options typically written without a value, you can pass
+ the value TRUE, since all such options are Booleans.
</para>
<para>
<para>
This is a Boolean option. If true, it specifies that values of the
column should not be matched against the null string (that is, the
- file-level <literal>null</literal> option). This has the same effect
- as listing the column in <command>COPY</>'s
+ table-level <literal>null</literal> option). This has the same effect
+ as listing the column in <command>COPY</command>'s
<literal>FORCE_NOT_NULL</literal> option.
</para>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>force_null</literal></term>
+
+ <listitem>
+ <para>
+ This is a Boolean option. If true, it specifies that values of the
+ column which match the null string are returned as <literal>NULL</literal>
+ even if the value is quoted. Without this option, only unquoted
+ values matching the null string are returned as <literal>NULL</literal>.
+ This has the same effect as listing the column in
+ <command>COPY</command>'s <literal>FORCE_NULL</literal> option.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
<para>
- <command>COPY</>'s <literal>OIDS</literal> and
- <literal>FORCE_QUOTE</literal> options are currently not supported by
- <literal>file_fdw</>.
+ <command>COPY</command>'s <literal>FORCE_QUOTE</literal> option is
+ currently not supported by <literal>file_fdw</literal>.
</para>
<para>
These options can only be specified for a foreign table or its columns, not
- in the options of the <literal>file_fdw</> foreign-data wrapper, nor in the
+ in the options of the <literal>file_fdw</literal> foreign-data wrapper, nor in the
options of a server or user mapping using the wrapper.
</para>
<para>
- Changing table-level options requires superuser privileges, for security
- reasons: only a superuser should be able to determine which file is read.
- In principle non-superusers could be allowed to change the other options,
- but that's not supported at present.
+ Changing table-level options requires being a superuser or having the privileges
+ of the default role <literal>pg_read_server_files</literal> (to use a filename) or
+ the default role <literal>pg_execute_server_program</literal> (to use a program),
+ for security reasons: only certain users should be able to control which file is
+ read or which program is run. In principle regular users could be allowed to
+ change the other options, but that's not supported at present.
+ </para>
+
+ <para>
+ When specifying the <literal>program</literal> option, keep in mind that the option
+ string is executed by the shell. If you need to pass any arguments to the
+ command that come from an untrusted source, you must be careful to strip or
+ escape any characters that might have special meaning to the shell.
+ For security reasons, it is best to use a fixed command string, or at least
+ avoid passing any user input in it.
</para>
<para>
- For a foreign table using <literal>file_fdw</>, <command>EXPLAIN</> shows
- the name of the file to be read. Unless <literal>COSTS OFF</> is
+ For a foreign table using <literal>file_fdw</literal>, <command>EXPLAIN</command> shows
+ the name of the file to be read or program to be run.
+ For a file, unless <literal>COSTS OFF</literal> is
specified, the file size (in bytes) is shown as well.
</para>
<title id="csvlog-fdw">Create a Foreign Table for PostgreSQL CSV Logs</title>
<para>
- One of the obvious uses for the <literal>file_fdw</> is to make
+ One of the obvious uses for <literal>file_fdw</literal> is to make
the PostgreSQL activity log available as a table for querying. To
do this, first you must be logging to a CSV file, which here we
- will call <literal>pglog.csv</>. First, install <literal>file_fdw</>
+ will call <literal>pglog.csv</literal>. First, install <literal>file_fdw</literal>
as an extension:
</para>
<para>
Now you are ready to create the foreign data table. Using the
- <command>CREATE FOREIGN TABLE</> command, you will need to define
+ <command>CREATE FOREIGN TABLE</command> command, you will need to define
the columns for the table, the CSV file name, and its format:
<programlisting>
location text,
application_name text
) SERVER pglog
-OPTIONS ( filename '/home/josh/9.1/data/pg_log/pglog.csv', format 'csv' );
+OPTIONS ( filename '/home/josh/data/log/pglog.csv', format 'csv' );
</programlisting>
</para>
projects / postgresql / blobdiff