1 <!-- doc/src/sgml/file-fdw.sgml -->
3 <sect1 id="file-fdw" xreflabel="file_fdw">
4 <title>file_fdw</title>
6 <indexterm zone="file-fdw">
7 <primary>file_fdw</primary>
11 The <filename>file_fdw</> 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.
21 A foreign table created using this wrapper can have the following options:
27 <term><literal>filename</literal></term>
31 Specifies the file to be read. Must be an absolute path name.
32 Either <literal>filename</literal> or <literal>program</literal> must be
33 specified, but not both.
39 <term><literal>program</literal></term>
43 Specifies the command to be executed. The standard output of this
44 command will be read as though <command>COPY FROM PROGRAM</> were used.
45 Either <literal>program</literal> or <literal>filename</literal> must be
46 specified, but not both.
52 <term><literal>format</literal></term>
56 Specifies the data format,
57 the same as <command>COPY</>'s <literal>FORMAT</literal> option.
63 <term><literal>header</literal></term>
67 Specifies whether the data has a header line,
68 the same as <command>COPY</>'s <literal>HEADER</literal> option.
74 <term><literal>delimiter</literal></term>
78 Specifies the data delimiter character,
79 the same as <command>COPY</>'s <literal>DELIMITER</literal> option.
85 <term><literal>quote</literal></term>
89 Specifies the data quote character,
90 the same as <command>COPY</>'s <literal>QUOTE</literal> option.
96 <term><literal>escape</literal></term>
100 Specifies the data escape character,
101 the same as <command>COPY</>'s <literal>ESCAPE</literal> option.
107 <term><literal>null</literal></term>
111 Specifies the data null string,
112 the same as <command>COPY</>'s <literal>NULL</literal> option.
118 <term><literal>encoding</literal></term>
122 Specifies the data encoding,
123 the same as <command>COPY</>'s <literal>ENCODING</literal> option.
131 Note that while <command>COPY</> allows options such as <literal>HEADER</>
132 to be specified without a corresponding value, the foreign table option
133 syntax requires a value to be present in all cases. To activate
134 <command>COPY</> options typically written without a value, you can pass
135 the value TRUE, since all such options are Booleans.
139 A column of a foreign table created using this wrapper can have the
146 <term><literal>force_not_null</literal></term>
150 This is a Boolean option. If true, it specifies that values of the
151 column should not be matched against the null string (that is, the
152 table-level <literal>null</literal> option). This has the same effect
153 as listing the column in <command>COPY</>'s
154 <literal>FORCE_NOT_NULL</literal> option.
160 <term><literal>force_null</literal></term>
164 This is a Boolean option. If true, it specifies that values of the
165 column which match the null string are returned as <literal>NULL</>
166 even if the value is quoted. Without this option, only unquoted
167 values matching the null string are returned as <literal>NULL</>.
168 This has the same effect as listing the column in
169 <command>COPY</>'s <literal>FORCE_NULL</literal> option.
177 <command>COPY</>'s <literal>OIDS</literal> and
178 <literal>FORCE_QUOTE</literal> options are currently not supported by
179 <literal>file_fdw</>.
183 These options can only be specified for a foreign table or its columns, not
184 in the options of the <literal>file_fdw</> foreign-data wrapper, nor in the
185 options of a server or user mapping using the wrapper.
189 Changing table-level options requires superuser privileges, for security
190 reasons: only a superuser should be able to control which file is read
191 or which program is run. In principle non-superusers could be allowed to
192 change the other options, but that's not supported at present.
196 When specifying the <literal>program</> option, keep in mind that the option
197 string is executed by the shell. If you need to pass any arguments to the
198 command that come from an untrusted source, you must be careful to strip or
199 escape any characters that might have special meaning to the shell.
200 For security reasons, it is best to use a fixed command string, or at least
201 avoid passing any user input in it.
205 For a foreign table using <literal>file_fdw</>, <command>EXPLAIN</> shows
206 the name of the file to be read or program to be run.
207 For a file, unless <literal>COSTS OFF</> is
208 specified, the file size (in bytes) is shown as well.
212 <title id="csvlog-fdw">Create a Foreign Table for PostgreSQL CSV Logs</title>
215 One of the obvious uses for <literal>file_fdw</> is to make
216 the PostgreSQL activity log available as a table for querying. To
217 do this, first you must be logging to a CSV file, which here we
218 will call <literal>pglog.csv</>. First, install <literal>file_fdw</>
223 CREATE EXTENSION file_fdw;
227 Then create a foreign server:
230 CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;
235 Now you are ready to create the foreign data table. Using the
236 <command>CREATE FOREIGN TABLE</> command, you will need to define
237 the columns for the table, the CSV file name, and its format:
240 CREATE FOREIGN TABLE pglog (
241 log_time timestamp(3) with time zone,
245 connection_from text,
247 session_line_num bigint,
249 session_start_time timestamp with time zone,
250 virtual_transaction_id text,
251 transaction_id bigint,
258 internal_query_pos integer,
263 application_name text
265 OPTIONS ( filename '/home/josh/data/log/pglog.csv', format 'csv' );
270 That's it — now you can query your log directly. In production, of
271 course, you would need to define some way to deal with log rotation.