Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id 3CD751E74DA1
	for <pgsql-docs@postgresql.org>; Tue, 29 Nov 2011 20:29:10 -0400 (AST)
Received: from momjian.us ([72.94.173.45])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <bruce@momjian.us>) id 1RVY2t-0002H0-Ik
	for pgsql-docs@postgresql.org; Wed, 30 Nov 2011 00:29:09 +0000
Received: (from bruce@localhost) by momjian.us (8.11.6/8.11.6) id
 pAU0Spj08021;
	Tue, 29 Nov 2011 19:28:51 -0500 (EST)
From: Bruce Momjian <bruce@momjian.us>
Message-Id: <201111300028.pAU0Spj08021@momjian.us>
Subject: Re: File_FDW with example
In-Reply-To: <1054372046.268838.1308273696804.JavaMail.root@mail-1.01.com>
To: Joshua Berkus <josh@agliodbs.com>
Date: Tue, 29 Nov 2011 19:28:51 -0500 (EST)
CC: pgsql-docs@postgresql.org
X-Mailer: ELM [version 2.4ME+ PL124 (25)]
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="ELM1322612931-26840-1_"
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: -3.1 (---)
X-Archive-Number: 201111/28
X-Sequence-Number: 7103


--ELM1322612931-26840-1_
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset="US-ASCII"

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. +

--ELM1322612931-26840-1_
Content-Transfer-Encoding: 7bit
Content-Type: text/x-diff
Content-Disposition: inline; filename="/rtmp/csv"

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 &mdash; 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>

--ELM1322612931-26840-1_--