Re: File_FDW with example - Mailing list pgsql-docs

Joshua Berkus wrote:
> (apologies for prior incomplete post.  Webmail spazzed on me).
>
> Attached is a version of file_FDW.sgml which contains a complete example of how to use it to read your postgresql csv
logs. I think this does some neat tying together of how to use FDWs that the docs are currently lacking. 

I have merged your file_fdw example into our docs --- patch attached.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +
diff --git a/doc/src/sgml/file-fdw.sgml b/doc/src/sgml/file-fdw.sgml
new file mode 100644
index dd712e9..900b055
*** a/doc/src/sgml/file-fdw.sgml
--- b/doc/src/sgml/file-fdw.sgml
***************
*** 158,161 ****
--- 158,233 ----
    specified, the file size (in bytes) is shown as well.
   </para>

+  <example>
+  <title id="csvlog-fdw">Create a Foreign Table for PostgreSQL CSV Logs</title>
+
+   <para>
+    One of the obvious uses for the <literal>file_fdw</> is to make
+    the PostgreSQL activity log available as a table for querying.  To
+    do this, first you must be logging to a CSV file, which here we
+    will call <literal>pglog.csv</>.  First, install <literal>file_fdw</>
+    as an extension:
+   </para>
+
+ <programlisting>
+ CREATE EXTENSION file_fdw;
+ </programlisting>
+
+   <para>
+    Next, create the foreign data wrapper:
+
+ <programlisting>
+ CREATE FOREIGN DATA WRAPPER file_fdw HANDLER file_fdw_handler;
+ </programlisting>
+   </para>
+
+   <para>
+    Then create a foreign data server:
+
+ <programlisting>
+ CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;
+ </programlisting>
+   </para>
+
+   <para>
+    Now you are ready to create the foreign data table.  Using the
+    <command>CREATE FOREIGN TABLE</> command, you will need to define
+    the columns for the table, the CSV filename, and its format:
+
+ <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
+ ) SERVER pglog
+ OPTIONS ( filename '/home/josh/9.1/data/pg_log/pglog.csv', format 'csv' );
+ </programlisting>
+   </para>
+
+   <para>
+    That's it — now you can query your log directly. In production, of course,
+    you would need to define some way to adjust to log rotation.
+   </para>
+  </example>
+
  </sect1>