doc/src/sgml/file-fdw.sgml

   1 <!-- doc/src/sgml/file-fdw.sgml -->
   2
   3 <sect1 id="file-fdw" xreflabel="file_fdw">
   4  <title>file_fdw &mdash; access data files in the server's file system</title>
   5
   6  <indexterm zone="file-fdw">
   7   <primary>file_fdw</primary>
   8  </indexterm>
   9
  10  <para>
  11   The <filename>file_fdw</filename> module provides the foreign-data wrapper
  12   <function>file_fdw</function>, which can be used to access data
  13   files in the server's file system, or to execute programs on the server
  14   and read their output.  The data file or program output must be in a format
  15   that can be read by <command>COPY FROM</command>;
  16   see <xref linkend="sql-copy"/> for details.
  17   Access to data files is currently read-only.
  18  </para>
  19
  20  <para>
  21   A foreign table created using this wrapper can have the following options:
  22  </para>
  23
  24  <variablelist>
  25
  26   <varlistentry>
  27    <term><literal>filename</literal></term>
  28
  29    <listitem>
  30     <para>
  31      Specifies the file to be read.  Relative paths are relative to the
  32      data directory.
  33      Either <literal>filename</literal> or <literal>program</literal> must be
  34      specified, but not both.
  35     </para>
  36    </listitem>
  37   </varlistentry>
  38
  39   <varlistentry>
  40    <term><literal>program</literal></term>
  41
  42    <listitem>
  43     <para>
  44      Specifies the command to be executed.  The standard output of this
  45      command will be read as though <command>COPY FROM PROGRAM</command> were used.
  46      Either <literal>program</literal> or <literal>filename</literal> must be
  47      specified, but not both.
  48     </para>
  49    </listitem>
  50   </varlistentry>
  51
  52   <varlistentry>
  53    <term><literal>format</literal></term>
  54
  55    <listitem>
  56     <para>
  57      Specifies the data format,
  58      the same as <command>COPY</command>'s <literal>FORMAT</literal> option.
  59     </para>
  60    </listitem>
  61   </varlistentry>
  62
  63   <varlistentry>
  64    <term><literal>header</literal></term>
  65
  66    <listitem>
  67     <para>
  68      Specifies whether the data has a header line,
  69      the same as <command>COPY</command>'s <literal>HEADER</literal> option.
  70     </para>
  71    </listitem>
  72   </varlistentry>
  73
  74   <varlistentry>
  75    <term><literal>delimiter</literal></term>
  76
  77    <listitem>
  78     <para>
  79      Specifies the data delimiter character,
  80      the same as <command>COPY</command>'s <literal>DELIMITER</literal> option.
  81     </para>
  82    </listitem>
  83   </varlistentry>
  84
  85   <varlistentry>
  86    <term><literal>quote</literal></term>
  87
  88    <listitem>
  89     <para>
  90      Specifies the data quote character,
  91      the same as <command>COPY</command>'s <literal>QUOTE</literal> option.
  92     </para>
  93    </listitem>
  94   </varlistentry>
  95
  96   <varlistentry>
  97    <term><literal>escape</literal></term>
  98
  99    <listitem>
 100     <para>
 101      Specifies the data escape character,
 102      the same as <command>COPY</command>'s <literal>ESCAPE</literal> option.
 103     </para>
 104    </listitem>
 105   </varlistentry>
 106
 107   <varlistentry>
 108    <term><literal>null</literal></term>
 109
 110    <listitem>
 111     <para>
 112      Specifies the data null string,
 113      the same as <command>COPY</command>'s <literal>NULL</literal> option.
 114     </para>
 115    </listitem>
 116   </varlistentry>
 117
 118   <varlistentry>
 119    <term><literal>encoding</literal></term>
 120
 121    <listitem>
 122     <para>
 123      Specifies the data encoding,
 124      the same as <command>COPY</command>'s <literal>ENCODING</literal> option.
 125     </para>
 126    </listitem>
 127   </varlistentry>
 128
 129   <varlistentry>
 130    <term><literal>on_error</literal></term>
 131
 132    <listitem>
 133     <para>
 134      Specifies how to behave when encountering an error converting a column's
 135      input value into its data type,
 136      the same as <command>COPY</command>'s <literal>ON_ERROR</literal> option.
 137     </para>
 138    </listitem>
 139   </varlistentry>
 140
 141   <varlistentry>
 142    <term><literal>reject_limit</literal></term>
 143
 144    <listitem>
 145     <para>
 146      Specifies the maximum number of errors tolerated while converting a column's
 147      input value to its data type, the same as <command>COPY</command>'s
 148     <literal>REJECT_LIMIT</literal> option.
 149     </para>
 150    </listitem>
 151   </varlistentry>
 152
 153   <varlistentry>
 154    <term><literal>log_verbosity</literal></term>
 155
 156    <listitem>
 157     <para>
 158      Specifies the amount of messages emitted by <literal>file_fdw</literal>,
 159      the same as <command>COPY</command>'s <literal>LOG_VERBOSITY</literal> option.
 160     </para>
 161    </listitem>
 162   </varlistentry>
 163
 164  </variablelist>
 165
 166  <para>
 167   Note that while <command>COPY</command> allows options such as <literal>HEADER</literal>
 168   to be specified without a corresponding value, the foreign table option
 169   syntax requires a value to be present in all cases.  To activate
 170   <command>COPY</command> options typically written without a value, you can pass
 171   the value TRUE, since all such options are Booleans.
 172  </para>
 173
 174  <para>
 175   A column of a foreign table created using this wrapper can have the
 176   following options:
 177  </para>
 178
 179  <variablelist>
 180
 181   <varlistentry>
 182    <term><literal>force_not_null</literal></term>
 183
 184    <listitem>
 185     <para>
 186      This is a Boolean option.  If true, it specifies that values of the
 187      column should not be matched against the null string (that is, the
 188      table-level <literal>null</literal> option).  This has the same effect
 189      as listing the column in <command>COPY</command>'s
 190      <literal>FORCE_NOT_NULL</literal> option.
 191     </para>
 192    </listitem>
 193   </varlistentry>
 194
 195   <varlistentry>
 196    <term><literal>force_null</literal></term>
 197
 198    <listitem>
 199     <para>
 200      This is a Boolean option.  If true, it specifies that values of the
 201      column which match the null string are returned as <literal>NULL</literal>
 202      even if the value is quoted. Without this option, only unquoted
 203      values matching the null string are returned as <literal>NULL</literal>.
 204      This has the same effect  as listing the column in
 205      <command>COPY</command>'s <literal>FORCE_NULL</literal> option.
 206     </para>
 207    </listitem>
 208   </varlistentry>
 209
 210  </variablelist>
 211
 212  <para>
 213   <command>COPY</command>'s <literal>FORCE_QUOTE</literal> option is
 214   currently not supported by <literal>file_fdw</literal>.
 215  </para>
 216
 217  <para>
 218   These options can only be specified for a foreign table or its columns, not
 219   in the options of the <literal>file_fdw</literal> foreign-data wrapper, nor in the
 220   options of a server or user mapping using the wrapper.
 221  </para>
 222
 223  <para>
 224   Changing table-level options requires being a superuser or having the privileges
 225   of the role <literal>pg_read_server_files</literal> (to use a filename) or
 226   the role <literal>pg_execute_server_program</literal> (to use a program),
 227   for security reasons: only certain users should be able to control which file is
 228   read or which program is run.  In principle regular users could be allowed to
 229   change the other options, but that's not supported at present.
 230  </para>
 231
 232  <para>
 233   When specifying the <literal>program</literal> option, keep in mind that the option
 234   string is executed by the shell.  If you need to pass any arguments to the
 235   command that come from an untrusted source, you must be careful to strip or
 236   escape any characters that might have special meaning to the shell.
 237   For security reasons, it is best to use a fixed command string, or at least
 238   avoid passing any user input in it.
 239  </para>
 240
 241  <para>
 242   For a foreign table using <literal>file_fdw</literal>, <command>EXPLAIN</command> shows
 243   the name of the file to be read or program to be run.
 244   For a file, unless <literal>COSTS OFF</literal> is
 245   specified, the file size (in bytes) is shown as well.
 246  </para>
 247
 248  <example>
 249   <title>Create a Foreign Table for PostgreSQL CSV Logs</title>
 250
 251   <para>
 252    One of the obvious uses for <literal>file_fdw</literal> is to make
 253    the PostgreSQL activity log available as a table for querying.  To
 254    do this, first you must be <link
 255    linkend="runtime-config-logging-csvlog">logging to a CSV file,</link>
 256    which here we
 257    will call <literal>pglog.csv</literal>.  First, install <literal>file_fdw</literal>
 258    as an extension:
 259   </para>
 260
 261 <programlisting>
 262 CREATE EXTENSION file_fdw;
 263 </programlisting>
 264
 265   <para>
 266    Then create a foreign server:
 267
 268 <programlisting>
 269 CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;
 270 </programlisting>
 271   </para>
 272
 273   <para>
 274    Now you are ready to create the foreign data table.  Using the
 275    <command>CREATE FOREIGN TABLE</command> command, you will need to define
 276    the columns for the table, the CSV file name, and its format:
 277
 278 <programlisting>
 279 CREATE FOREIGN TABLE pglog (
 280   log_time timestamp(3) with time zone,
 281   user_name text,
 282   database_name text,
 283   process_id integer,
 284   connection_from text,
 285   session_id text,
 286   session_line_num bigint,
 287   command_tag text,
 288   session_start_time timestamp with time zone,
 289   virtual_transaction_id text,
 290   transaction_id bigint,
 291   error_severity text,
 292   sql_state_code text,
 293   message text,
 294   detail text,
 295   hint text,
 296   internal_query text,
 297   internal_query_pos integer,
 298   context text,
 299   query text,
 300   query_pos integer,
 301   location text,
 302   application_name text,
 303   backend_type text,
 304   leader_pid integer,
 305   query_id bigint
 306 ) SERVER pglog
 307 OPTIONS ( filename 'log/pglog.csv', format 'csv' );
 308 </programlisting>
 309   </para>
 310
 311   <para>
 312    That's it &mdash; now you can query your log directly. In production, of
 313    course, you would need to define some way to deal with log rotation.
 314   </para>
 315  </example>
 316
 317  <example>
 318   <title>Create a Foreign Table with an Option on a Column</title>
 319   <para>
 320    To set the <literal>force_null</literal> option for a column, use the
 321    <literal>OPTIONS</literal> keyword.
 322   </para>
 323 <programlisting>
 324 CREATE FOREIGN TABLE films (
 325  code char(5) NOT NULL,
 326  title text NOT NULL,
 327  rating text OPTIONS (force_null 'true')
 328 ) SERVER film_server
 329 OPTIONS ( filename 'films/db.csv', format 'csv' );
 330 </programlisting>
 331  </example>
 332
 333 </sect1>