diff options
Diffstat (limited to 'doc/src/sgml/html/file-fdw.html')
| -rw-r--r-- | doc/src/sgml/html/file-fdw.html | 145 |
1 files changed, 145 insertions, 0 deletions
diff --git a/doc/src/sgml/html/file-fdw.html b/doc/src/sgml/html/file-fdw.html new file mode 100644 index 0000000..4e39a51 --- /dev/null +++ b/doc/src/sgml/html/file-fdw.html @@ -0,0 +1,145 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>F.16. file_fdw</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="earthdistance.html" title="F.15. earthdistance" /><link rel="next" href="fuzzystrmatch.html" title="F.17. fuzzystrmatch" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">F.16. file_fdw</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="earthdistance.html" title="F.15. earthdistance">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules">Up</a></td><th width="60%" align="center">Appendix F. Additional Supplied Modules</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="fuzzystrmatch.html" title="F.17. fuzzystrmatch">Next</a></td></tr></table><hr /></div><div class="sect1" id="FILE-FDW"><div class="titlepage"><div><div><h2 class="title" style="clear: both">F.16. file_fdw</h2></div></div></div><a id="id-1.11.7.25.2" class="indexterm"></a><p> + The <code class="filename">file_fdw</code> module provides the foreign-data wrapper + <code class="function">file_fdw</code>, which can be used to access data + 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 <code class="command">COPY FROM</code>; + see <a class="xref" href="sql-copy.html" title="COPY"><span class="refentrytitle">COPY</span></a> for details. + Access to data files is currently read-only. + </p><p> + A foreign table created using this wrapper can have the following options: + </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">filename</code></span></dt><dd><p> + Specifies the file to be read. Relative paths are relative to the + data directory. + Either <code class="literal">filename</code> or <code class="literal">program</code> must be + specified, but not both. + </p></dd><dt><span class="term"><code class="literal">program</code></span></dt><dd><p> + Specifies the command to be executed. The standard output of this + command will be read as though <code class="command">COPY FROM PROGRAM</code> were used. + Either <code class="literal">program</code> or <code class="literal">filename</code> must be + specified, but not both. + </p></dd><dt><span class="term"><code class="literal">format</code></span></dt><dd><p> + Specifies the data format, + the same as <code class="command">COPY</code>'s <code class="literal">FORMAT</code> option. + </p></dd><dt><span class="term"><code class="literal">header</code></span></dt><dd><p> + Specifies whether the data has a header line, + the same as <code class="command">COPY</code>'s <code class="literal">HEADER</code> option. + </p></dd><dt><span class="term"><code class="literal">delimiter</code></span></dt><dd><p> + Specifies the data delimiter character, + the same as <code class="command">COPY</code>'s <code class="literal">DELIMITER</code> option. + </p></dd><dt><span class="term"><code class="literal">quote</code></span></dt><dd><p> + Specifies the data quote character, + the same as <code class="command">COPY</code>'s <code class="literal">QUOTE</code> option. + </p></dd><dt><span class="term"><code class="literal">escape</code></span></dt><dd><p> + Specifies the data escape character, + the same as <code class="command">COPY</code>'s <code class="literal">ESCAPE</code> option. + </p></dd><dt><span class="term"><code class="literal">null</code></span></dt><dd><p> + Specifies the data null string, + the same as <code class="command">COPY</code>'s <code class="literal">NULL</code> option. + </p></dd><dt><span class="term"><code class="literal">encoding</code></span></dt><dd><p> + Specifies the data encoding, + the same as <code class="command">COPY</code>'s <code class="literal">ENCODING</code> option. + </p></dd></dl></div><p> + Note that while <code class="command">COPY</code> allows options such as <code class="literal">HEADER</code> + to be specified without a corresponding value, the foreign table option + syntax requires a value to be present in all cases. To activate + <code class="command">COPY</code> options typically written without a value, you can pass + the value TRUE, since all such options are Booleans. + </p><p> + A column of a foreign table created using this wrapper can have the + following options: + </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">force_not_null</code></span></dt><dd><p> + 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 + table-level <code class="literal">null</code> option). This has the same effect + as listing the column in <code class="command">COPY</code>'s + <code class="literal">FORCE_NOT_NULL</code> option. + </p></dd><dt><span class="term"><code class="literal">force_null</code></span></dt><dd><p> + This is a Boolean option. If true, it specifies that values of the + column which match the null string are returned as <code class="literal">NULL</code> + even if the value is quoted. Without this option, only unquoted + values matching the null string are returned as <code class="literal">NULL</code>. + This has the same effect as listing the column in + <code class="command">COPY</code>'s <code class="literal">FORCE_NULL</code> option. + </p></dd></dl></div><p> + <code class="command">COPY</code>'s <code class="literal">FORCE_QUOTE</code> option is + currently not supported by <code class="literal">file_fdw</code>. + </p><p> + These options can only be specified for a foreign table or its columns, not + in the options of the <code class="literal">file_fdw</code> foreign-data wrapper, nor in the + options of a server or user mapping using the wrapper. + </p><p> + Changing table-level options requires being a superuser or having the privileges + of the role <code class="literal">pg_read_server_files</code> (to use a filename) or + the role <code class="literal">pg_execute_server_program</code> (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. + </p><p> + When specifying the <code class="literal">program</code> 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. + </p><p> + For a foreign table using <code class="literal">file_fdw</code>, <code class="command">EXPLAIN</code> shows + the name of the file to be read or program to be run. + For a file, unless <code class="literal">COSTS OFF</code> is + specified, the file size (in bytes) is shown as well. + </p><div class="example" id="id-1.11.7.25.14"><p class="title"><strong>Example F.1. Create a Foreign Table for PostgreSQL CSV Logs</strong></p><div class="example-contents"><p> + One of the obvious uses for <code class="literal">file_fdw</code> is to make + the PostgreSQL activity log available as a table for querying. To + do this, first you must be <a class="link" href="runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG" title="20.8.4. Using CSV-Format Log Output">logging to a CSV file,</a> + which here we + will call <code class="literal">pglog.csv</code>. First, install <code class="literal">file_fdw</code> + as an extension: + </p><pre class="programlisting"> +CREATE EXTENSION file_fdw; +</pre><p> + Then create a foreign server: + +</p><pre class="programlisting"> +CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw; +</pre><p> + </p><p> + Now you are ready to create the foreign data table. Using the + <code class="command">CREATE FOREIGN TABLE</code> command, you will need to define + the columns for the table, the CSV file name, and its format: + +</p><pre class="programlisting"> +CREATE FOREIGN TABLE pglog ( + log_time timestamp(3) with time zone, + user_name text, + database_name text, + process_id integer, + connection_from text, + session_id text, + session_line_num bigint, + command_tag text, + session_start_time timestamp with time zone, + virtual_transaction_id text, + transaction_id bigint, + error_severity text, + sql_state_code text, + message text, + detail text, + hint text, + internal_query text, + internal_query_pos integer, + context text, + query text, + query_pos integer, + location text, + application_name text, + backend_type text, + leader_pid integer, + query_id bigint +) SERVER pglog +OPTIONS ( filename 'log/pglog.csv', format 'csv' ); +</pre><p> + </p><p> + That's it — now you can query your log directly. In production, of + course, you would need to define some way to deal with log rotation. + </p></div></div><br class="example-break" /></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="earthdistance.html" title="F.15. earthdistance">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="fuzzystrmatch.html" title="F.17. fuzzystrmatch">Next</a></td></tr><tr><td width="40%" align="left" valign="top">F.15. earthdistance </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> F.17. fuzzystrmatch</td></tr></table></div></body></html>
\ No newline at end of file |