Baoding-FenShuaJiang
/
Green_GoodERP18_FSJ_Standard
關註 1
收藏 0
Wiki 捐赠
gooderp18绿色标准版
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
分支: master
分支列表 標籤列表
${ item.name }
Create branch ${ searchTerm }
from 'master'
${ noResults }
Green_GoodERP18_FSJ_Standard/PostgreSQL/doc/postgresql/html/file-fdw.html

140 line
10KB
原始文件 永久連結 Blame 文件歷史 

  1. <?xml version="1.0" encoding="UTF-8" standalone="no"?>

  2. <!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.14. 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 V1.79.1" /><link rel="prev" href="earthdistance.html" title="F.13. earthdistance" /><link rel="next" href="fuzzystrmatch.html" title="F.15. fuzzystrmatch" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">F.14. file_fdw</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="earthdistance.html" title="F.13. 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 12.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="fuzzystrmatch.html" title="F.15. fuzzystrmatch">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="FILE-FDW"><div class="titlepage"><div><div><h2 class="title" style="clear: both">F.14. file_fdw</h2></div></div></div><a id="id-1.11.7.23.2" class="indexterm"></a><p>

  3.   The <code class="filename">file_fdw</code> module provides the foreign-data wrapper

  4.   <code class="function">file_fdw</code>, which can be used to access data

  5.   files in the server's file system, or to execute programs on the server

  6.   and read their output.  The data file or program output must be in a format

  7.   that can be read by <code class="command">COPY FROM</code>;

  8.   see <a class="xref" href="sql-copy.html" title="COPY"><span class="refentrytitle">COPY</span></a> for details.

  9.   Access to data files is currently read-only.

  10.  </p><p>

  11.   A foreign table created using this wrapper can have the following options:

  12.  </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">filename</code></span></dt><dd><p>

  13.      Specifies the file to be read.  Must be an absolute path name.

  14.      Either <code class="literal">filename</code> or <code class="literal">program</code> must be

  15.      specified, but not both.

  16.     </p></dd><dt><span class="term"><code class="literal">program</code></span></dt><dd><p>

  17.      Specifies the command to be executed.  The standard output of this

  18.      command will be read as though <code class="command">COPY FROM PROGRAM</code> were used.

  19.      Either <code class="literal">program</code> or <code class="literal">filename</code> must be

  20.      specified, but not both.

  21.     </p></dd><dt><span class="term"><code class="literal">format</code></span></dt><dd><p>

  22.      Specifies the data format,

  23.      the same as <code class="command">COPY</code>'s <code class="literal">FORMAT</code> option.

  24.     </p></dd><dt><span class="term"><code class="literal">header</code></span></dt><dd><p>

  25.      Specifies whether the data has a header line,

  26.      the same as <code class="command">COPY</code>'s <code class="literal">HEADER</code> option.

  27.     </p></dd><dt><span class="term"><code class="literal">delimiter</code></span></dt><dd><p>

  28.      Specifies the data delimiter character,

  29.      the same as <code class="command">COPY</code>'s <code class="literal">DELIMITER</code> option.

  30.     </p></dd><dt><span class="term"><code class="literal">quote</code></span></dt><dd><p>

  31.      Specifies the data quote character,

  32.      the same as <code class="command">COPY</code>'s <code class="literal">QUOTE</code> option.

  33.     </p></dd><dt><span class="term"><code class="literal">escape</code></span></dt><dd><p>

  34.      Specifies the data escape character,

  35.      the same as <code class="command">COPY</code>'s <code class="literal">ESCAPE</code> option.

  36.     </p></dd><dt><span class="term"><code class="literal">null</code></span></dt><dd><p>

  37.      Specifies the data null string,

  38.      the same as <code class="command">COPY</code>'s <code class="literal">NULL</code> option.

  39.     </p></dd><dt><span class="term"><code class="literal">encoding</code></span></dt><dd><p>

  40.      Specifies the data encoding,

  41.      the same as <code class="command">COPY</code>'s <code class="literal">ENCODING</code> option.

  42.     </p></dd></dl></div><p>

  43.   Note that while <code class="command">COPY</code> allows options such as <code class="literal">HEADER</code>

  44.   to be specified without a corresponding value, the foreign table option

  45.   syntax requires a value to be present in all cases.  To activate

  46.   <code class="command">COPY</code> options typically written without a value, you can pass

  47.   the value TRUE, since all such options are Booleans.

  48.  </p><p>

  49.   A column of a foreign table created using this wrapper can have the

  50.   following options:

  51.  </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">force_not_null</code></span></dt><dd><p>

  52.      This is a Boolean option.  If true, it specifies that values of the

  53.      column should not be matched against the null string (that is, the

  54.      table-level <code class="literal">null</code> option).  This has the same effect

  55.      as listing the column in <code class="command">COPY</code>'s

  56.      <code class="literal">FORCE_NOT_NULL</code> option.

  57.     </p></dd><dt><span class="term"><code class="literal">force_null</code></span></dt><dd><p>

  58.      This is a Boolean option.  If true, it specifies that values of the

  59.      column which match the null string are returned as <code class="literal">NULL</code>

  60.      even if the value is quoted. Without this option, only unquoted

  61.      values matching the null string are returned as <code class="literal">NULL</code>.

  62.      This has the same effect  as listing the column in

  63.      <code class="command">COPY</code>'s <code class="literal">FORCE_NULL</code> option.

  64.     </p></dd></dl></div><p>

  65.   <code class="command">COPY</code>'s <code class="literal">FORCE_QUOTE</code> option is

  66.   currently not supported by <code class="literal">file_fdw</code>.

  67.  </p><p>

  68.   These options can only be specified for a foreign table or its columns, not

  69.   in the options of the <code class="literal">file_fdw</code> foreign-data wrapper, nor in the

  70.   options of a server or user mapping using the wrapper.

  71.  </p><p>

  72.   Changing table-level options requires being a superuser or having the privileges

  73.   of the default role <code class="literal">pg_read_server_files</code> (to use a filename) or

  74.   the default role <code class="literal">pg_execute_server_program</code> (to use a program),

  75.   for security reasons: only certain users should be able to control which file is

  76.   read or which program is run.  In principle regular users could be allowed to

  77.   change the other options, but that's not supported at present.

  78.  </p><p>

  79.   When specifying the <code class="literal">program</code> option, keep in mind that the option

  80.   string is executed by the shell.  If you need to pass any arguments to the

  81.   command that come from an untrusted source, you must be careful to strip or

  82.   escape any characters that might have special meaning to the shell.

  83.   For security reasons, it is best to use a fixed command string, or at least

  84.   avoid passing any user input in it.

  85.  </p><p>

  86.   For a foreign table using <code class="literal">file_fdw</code>, <code class="command">EXPLAIN</code> shows

  87.   the name of the file to be read or program to be run.

  88.   For a file, unless <code class="literal">COSTS OFF</code> is

  89.   specified, the file size (in bytes) is shown as well.

  90.  </p><div class="example" id="id-1.11.7.23.14"><p class="title"><strong>Example F.1. Create a Foreign Table for PostgreSQL CSV Logs</strong></p><div class="example-contents"><p>

  91.    One of the obvious uses for <code class="literal">file_fdw</code> is to make

  92.    the PostgreSQL activity log available as a table for querying.  To

  93.    do this, first you must be logging to a CSV file, which here we

  94.    will call <code class="literal">pglog.csv</code>.  First, install <code class="literal">file_fdw</code>

  95.    as an extension:

  96.   </p><pre class="programlisting">

  97. CREATE EXTENSION file_fdw;

  98. </pre><p>

  99.    Then create a foreign server:

  100. 

  101. </p><pre class="programlisting">

  102. CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;

  103. </pre><p>

  104.   </p><p>

  105.    Now you are ready to create the foreign data table.  Using the

  106.    <code class="command">CREATE FOREIGN TABLE</code> command, you will need to define

  107.    the columns for the table, the CSV file name, and its format:

  108. 

  109. </p><pre class="programlisting">

  110. CREATE FOREIGN TABLE pglog (

  111.   log_time timestamp(3) with time zone,

  112.   user_name text,

  113.   database_name text,

  114.   process_id integer,

  115.   connection_from text,

  116.   session_id text,

  117.   session_line_num bigint,

  118.   command_tag text,

  119.   session_start_time timestamp with time zone,

  120.   virtual_transaction_id text,

  121.   transaction_id bigint,

  122.   error_severity text,

  123.   sql_state_code text,

  124.   message text,

  125.   detail text,

  126.   hint text,

  127.   internal_query text,

  128.   internal_query_pos integer,

  129.   context text,

  130.   query text,

  131.   query_pos integer,

  132.   location text,

  133.   application_name text

  134. ) SERVER pglog

  135. OPTIONS ( filename '/home/josh/data/log/pglog.csv', format 'csv' );

  136. </pre><p>

  137.   </p><p>

  138.    That's it — now you can query your log directly. In production, of

  139.    course, you would need to define some way to deal with log rotation.

  140.   </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">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="contrib.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="fuzzystrmatch.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">F.13. earthdistance </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> F.15. fuzzystrmatch</td></tr></table></div></body></html>
上海开阖软件有限公司 沪ICP备12045867号-1