codekingpro/portable-devtools
115k
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.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.3 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>3 The <code class="filename">file_fdw</code> module provides the foreign-data wrapper4 <code class="function">file_fdw</code>, which can be used to access data5 files in the server's file system, or to execute programs on the server6 and read their output. The data file or program output must be in a format7 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. Relative paths are relative to the14 data directory.15 Either <code class="literal">filename</code> or <code class="literal">program</code> must be16 specified, but not both.17 </p></dd><dt><span class="term"><code class="literal">program</code></span></dt><dd><p>18 Specifies the command to be executed. The standard output of this19 command will be read as though <code class="command">COPY FROM PROGRAM</code> were used.20 Either <code class="literal">program</code> or <code class="literal">filename</code> must be21 specified, but not both.22 </p></dd><dt><span class="term"><code class="literal">format</code></span></dt><dd><p>23 Specifies the data format,24 the same as <code class="command">COPY</code>'s <code class="literal">FORMAT</code> option.25 </p></dd><dt><span class="term"><code class="literal">header</code></span></dt><dd><p>26 Specifies whether the data has a header line,27 the same as <code class="command">COPY</code>'s <code class="literal">HEADER</code> option.28 </p></dd><dt><span class="term"><code class="literal">delimiter</code></span></dt><dd><p>29 Specifies the data delimiter character,30 the same as <code class="command">COPY</code>'s <code class="literal">DELIMITER</code> option.31 </p></dd><dt><span class="term"><code class="literal">quote</code></span></dt><dd><p>32 Specifies the data quote character,33 the same as <code class="command">COPY</code>'s <code class="literal">QUOTE</code> option.34 </p></dd><dt><span class="term"><code class="literal">escape</code></span></dt><dd><p>35 Specifies the data escape character,36 the same as <code class="command">COPY</code>'s <code class="literal">ESCAPE</code> option.37 </p></dd><dt><span class="term"><code class="literal">null</code></span></dt><dd><p>38 Specifies the data null string,39 the same as <code class="command">COPY</code>'s <code class="literal">NULL</code> option.40 </p></dd><dt><span class="term"><code class="literal">encoding</code></span></dt><dd><p>41 Specifies the data encoding,42 the same as <code class="command">COPY</code>'s <code class="literal">ENCODING</code> option.43 </p></dd></dl></div><p>44 Note that while <code class="command">COPY</code> allows options such as <code class="literal">HEADER</code>45 to be specified without a corresponding value, the foreign table option46 syntax requires a value to be present in all cases. To activate47 <code class="command">COPY</code> options typically written without a value, you can pass48 the value TRUE, since all such options are Booleans.49 </p><p>50 A column of a foreign table created using this wrapper can have the51 following options:52 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">force_not_null</code></span></dt><dd><p>53 This is a Boolean option. If true, it specifies that values of the54 column should not be matched against the null string (that is, the55 table-level <code class="literal">null</code> option). This has the same effect56 as listing the column in <code class="command">COPY</code>'s57 <code class="literal">FORCE_NOT_NULL</code> option.58 </p></dd><dt><span class="term"><code class="literal">force_null</code></span></dt><dd><p>59 This is a Boolean option. If true, it specifies that values of the60 column which match the null string are returned as <code class="literal">NULL</code>61 even if the value is quoted. Without this option, only unquoted62 values matching the null string are returned as <code class="literal">NULL</code>.63 This has the same effect as listing the column in64 <code class="command">COPY</code>'s <code class="literal">FORCE_NULL</code> option.65 </p></dd></dl></div><p>66 <code class="command">COPY</code>'s <code class="literal">FORCE_QUOTE</code> option is67 currently not supported by <code class="literal">file_fdw</code>.68 </p><p>69 These options can only be specified for a foreign table or its columns, not70 in the options of the <code class="literal">file_fdw</code> foreign-data wrapper, nor in the71 options of a server or user mapping using the wrapper.72 </p><p>73 Changing table-level options requires being a superuser or having the privileges74 of the role <code class="literal">pg_read_server_files</code> (to use a filename) or75 the role <code class="literal">pg_execute_server_program</code> (to use a program),76 for security reasons: only certain users should be able to control which file is77 read or which program is run. In principle regular users could be allowed to78 change the other options, but that's not supported at present.79 </p><p>80 When specifying the <code class="literal">program</code> option, keep in mind that the option81 string is executed by the shell. If you need to pass any arguments to the82 command that come from an untrusted source, you must be careful to strip or83 escape any characters that might have special meaning to the shell.84 For security reasons, it is best to use a fixed command string, or at least85 avoid passing any user input in it.86 </p><p>87 For a foreign table using <code class="literal">file_fdw</code>, <code class="command">EXPLAIN</code> shows88 the name of the file to be read or program to be run.89 For a file, unless <code class="literal">COSTS OFF</code> is90 specified, the file size (in bytes) is shown as well.91 </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>92 One of the obvious uses for <code class="literal">file_fdw</code> is to make93 the PostgreSQL activity log available as a table for querying. To94 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>95 which here we96 will call <code class="literal">pglog.csv</code>. First, install <code class="literal">file_fdw</code>97 as an extension:98 </p><pre class="programlisting">99CREATE EXTENSION file_fdw;100</pre><p>101 Then create a foreign server:102 103</p><pre class="programlisting">104CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;105</pre><p>106 </p><p>107 Now you are ready to create the foreign data table. Using the108 <code class="command">CREATE FOREIGN TABLE</code> command, you will need to define109 the columns for the table, the CSV file name, and its format:110 111</p><pre class="programlisting">112CREATE FOREIGN TABLE pglog (113 log_time timestamp(3) with time zone,114 user_name text,115 database_name text,116 process_id integer,117 connection_from text,118 session_id text,119 session_line_num bigint,120 command_tag text,121 session_start_time timestamp with time zone,122 virtual_transaction_id text,123 transaction_id bigint,124 error_severity text,125 sql_state_code text,126 message text,127 detail text,128 hint text,129 internal_query text,130 internal_query_pos integer,131 context text,132 query text,133 query_pos integer,134 location text,135 application_name text,136 backend_type text,137 leader_pid integer,138 query_id bigint139) SERVER pglog140OPTIONS ( filename 'log/pglog.csv', format 'csv' );141</pre><p>142 </p><p>143 That's it — now you can query your log directly. In production, of144 course, you would need to define some way to deal with log rotation.145 </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.3 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>