summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/file-fdw.html
diff options
context:
space:
mode:
Diffstat (limited to 'doc/src/sgml/html/file-fdw.html')
-rw-r--r--doc/src/sgml/html/file-fdw.html145
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..3cc5dde
--- /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 — access data files in the server's file system</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 — calculate great-circle distances" /><link rel="next" href="fuzzystrmatch.html" title="F.17. fuzzystrmatch — determine string similarities and distance" /></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 — access data files in the server's file system</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="earthdistance.html" title="F.15. earthdistance — calculate great-circle distances">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules and Extensions">Up</a></td><th width="60%" align="center">Appendix F. Additional Supplied Modules and Extensions</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.2 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="fuzzystrmatch.html" title="F.17. fuzzystrmatch — determine string similarities and distance">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 — access data files in the server's file system <a href="#FILE-FDW" class="id_link">#</a></h2></div></div></div><a id="id-1.11.7.26.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.26.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 — calculate great-circle distances">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules and Extensions">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="fuzzystrmatch.html" title="F.17. fuzzystrmatch — determine string similarities and distance">Next</a></td></tr><tr><td width="40%" align="left" valign="top">F.15. earthdistance — calculate great-circle distances </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.2 Documentation">Home</a></td><td width="40%" align="right" valign="top"> F.17. fuzzystrmatch — determine string similarities and distance</td></tr></table></div></body></html> \ No newline at end of file
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-21 15:42:14 +0000