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  </variablelist>
 130
 131  <para>
 132   Note that while <command>COPY</command> allows options such as <literal>HEADER</literal>
 133   to be specified without a corresponding value, the foreign table option
 134   syntax requires a value to be present in all cases.  To activate
 135   <command>COPY</command> options typically written without a value, you can pass
 136   the value TRUE, since all such options are Booleans.
 137  </para>
 138
 139  <para>
 140   A column of a foreign table created using this wrapper can have the
 141   following options:
 142  </para>
 143
 144  <variablelist>
 145
 146   <varlistentry>
 147    <term><literal>force_not_null</literal></term>
 148
 149    <listitem>
 150     <para>
 151      This is a Boolean option.  If true, it specifies that values of the
 152      column should not be matched against the null string (that is, the
 153      table-level <literal>null</literal> option).  This has the same effect
 154      as listing the column in <command>COPY</command>'s
 155      <literal>FORCE_NOT_NULL</literal> option.
 156     </para>
 157    </listitem>
 158   </varlistentry>
 159
 160   <varlistentry>
 161    <term><literal>force_null</literal></term>
 162
 163    <listitem>
 164     <para>
 165      This is a Boolean option.  If true, it specifies that values of the
 166      column which match the null string are returned as <literal>NULL</literal>
 167      even if the value is quoted. Without this option, only unquoted
 168      values matching the null string are returned as <literal>NULL</literal>.
 169      This has the same effect  as listing the column in
 170      <command>COPY</command>'s <literal>FORCE_NULL</literal> option.
 171     </para>
 172    </listitem>
 173   </varlistentry>
 174
 175  </variablelist>
 176
 177  <para>
 178   <command>COPY</command>'s <literal>FORCE_QUOTE</literal> option is
 179   currently not supported by <literal>file_fdw</literal>.
 180  </para>
 181
 182  <para>
 183   These options can only be specified for a foreign table or its columns, not
 184   in the options of the <literal>file_fdw</literal> foreign-data wrapper, nor in the
 185   options of a server or user mapping using the wrapper.
 186  </para>
 187
 188  <para>
 189   Changing table-level options requires being a superuser or having the privileges
 190   of the role <literal>pg_read_server_files</literal> (to use a filename) or
 191   the role <literal>pg_execute_server_program</literal> (to use a program),
 192   for security reasons: only certain users should be able to control which file is
 193   read or which program is run.  In principle regular users could be allowed to
 194   change the other options, but that's not supported at present.
 195  </para>
 196
 197  <para>
 198   When specifying the <literal>program</literal> option, keep in mind that the option
 199   string is executed by the shell.  If you need to pass any arguments to the
 200   command that come from an untrusted source, you must be careful to strip or
 201   escape any characters that might have special meaning to the shell.
 202   For security reasons, it is best to use a fixed command string, or at least
 203   avoid passing any user input in it.
 204  </para>
 205
 206  <para>
 207   For a foreign table using <literal>file_fdw</literal>, <command>EXPLAIN</command> shows
 208   the name of the file to be read or program to be run.
 209   For a file, unless <literal>COSTS OFF</literal> is
 210   specified, the file size (in bytes) is shown as well.
 211  </para>
 212
 213  <example>
 214   <title>Create a Foreign Table for PostgreSQL CSV Logs</title>
 215
 216   <para>
 217    One of the obvious uses for <literal>file_fdw</literal> is to make
 218    the PostgreSQL activity log available as a table for querying.  To
 219    do this, first you must be <link
 220    linkend="runtime-config-logging-csvlog">logging to a CSV file,</link>
 221    which here we
 222    will call <literal>pglog.csv</literal>.  First, install <literal>file_fdw</literal>
 223    as an extension:
 224   </para>
 225
 226 <programlisting>
 227 CREATE EXTENSION file_fdw;
 228 </programlisting>
 229
 230   <para>
 231    Then create a foreign server:
 232
 233 <programlisting>
 234 CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;
 235 </programlisting>
 236   </para>
 237
 238   <para>
 239    Now you are ready to create the foreign data table.  Using the
 240    <command>CREATE FOREIGN TABLE</command> command, you will need to define
 241    the columns for the table, the CSV file name, and its format:
 242
 243 <programlisting>
 244 CREATE FOREIGN TABLE pglog (
 245   log_time timestamp(3) with time zone,
 246   user_name text,
 247   database_name text,
 248   process_id integer,
 249   connection_from text,
 250   session_id text,
 251   session_line_num bigint,
 252   command_tag text,
 253   session_start_time timestamp with time zone,
 254   virtual_transaction_id text,
 255   transaction_id bigint,
 256   error_severity text,
 257   sql_state_code text,
 258   message text,
 259   detail text,
 260   hint text,
 261   internal_query text,
 262   internal_query_pos integer,
 263   context text,
 264   query text,
 265   query_pos integer,
 266   location text,
 267   application_name text,
 268   backend_type text,
 269   leader_pid integer,
 270   query_id bigint
 271 ) SERVER pglog
 272 OPTIONS ( filename 'log/pglog.csv', format 'csv' );
 273 </programlisting>
 274   </para>
 275
 276   <para>
 277    That's it &mdash; now you can query your log directly. In production, of
 278    course, you would need to define some way to deal with log rotation.
 279   </para>
 280  </example>
 281
 282  <example>
 283   <title>Create a Foreign Table with an Option on a Column</title>
 284   <para>
 285    To set the <literal>force_null</literal> option for a column, use the
 286    <literal>OPTIONS</literal> keyword.
 287   </para>
 288 <programlisting>
 289 CREATE FOREIGN TABLE films (
 290  code char(5) NOT NULL,
 291  title text NOT NULL,
 292  rating text OPTIONS (force_null 'true')
 293 ) SERVER film_server
 294 OPTIONS ( filename 'films/db.csv', format 'csv' );
 295 </programlisting>
 296  </example>
 297
 298 </sect1>