Debian Codegraph @

doc/src/sgml/file-fdw.sgml

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