(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.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
San Francisco
<!-- doc/src/sgml/file-fdw.sgml -->
<sect1 id="file-fdw">
<title>file_fdw</title>
<indexterm zone="file-fdw">
<primary>file_fdw</primary>
</indexterm>
<para>
The <filename>file_fdw</> module provides the foreign-data wrapper
<function>file_fdw</function>, which can be used to access data
files in the server's filesystem. Data files must be in a format
that can be read by <command>COPY FROM</command>;
see <xref linkend="sql-copy"> for details.
</para>
<para>
A foreign table created using this wrapper can have the following options:
</para>
<variablelist>
<varlistentry>
<term><literal>filename</literal></term>
<listitem>
<para>
Specifies the file to be read. Required. Must be an absolute path name.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><literal>format</literal></term>
<listitem>
<para>
Specifies the file's format,
the same as <command>COPY</>'s <literal>FORMAT</literal> option.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><literal>header</literal></term>
<listitem>
<para>
Specifies whether the file has a header line,
the same as <command>COPY</>'s <literal>HEADER</literal> option.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><literal>delimiter</literal></term>
<listitem>
<para>
Specifies the file's delimiter character,
the same as <command>COPY</>'s <literal>DELIMITER</literal> option.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><literal>quote</literal></term>
<listitem>
<para>
Specifies the file's quote character,
the same as <command>COPY</>'s <literal>QUOTE</literal> option.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><literal>escape</literal></term>
<listitem>
<para>
Specifies the file's escape character,
the same as <command>COPY</>'s <literal>ESCAPE</literal> option.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><literal>null</literal></term>
<listitem>
<para>
Specifies the file's null string,
the same as <command>COPY</>'s <literal>NULL</literal> option.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><literal>encoding</literal></term>
<listitem>
<para>
Specifies the file's encoding.
the same as <command>COPY</>'s <literal>ENCODING</literal> option.
</para>
</listitem>
</varlistentry>
</variablelist>
<para>
<command>COPY</>'s <literal>OIDS</literal>, <literal>FORCE_QUOTE</literal>,
and <literal>FORCE_NOT_NULL</literal> options are currently not supported by
<literal>file_fdw</>.
</para>
<para>
These options can only be specified for a foreign table, not in the
options of the <literal>file_fdw</> foreign-data wrapper, nor in the
options of a server or user mapping using the wrapper.
</para>
<para>
Changing table-level options requires superuser privileges, for security
reasons: only a superuser should be able to determine which file is read.
In principle non-superusers could be allowed to change the other options,
but that's not supported at present.
</para>
<para>
For a foreign table using <literal>file_fdw</>, <command>EXPLAIN</> shows
the name of the file to be read. Unless <literal>COSTS OFF</> is
specified, the file size (in bytes) is shown as well.
</para>
<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 "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. In one command,
you will need to define both the columns for the table as well as
the filename for the file 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, not you can query your log directly. In production, of course,
you would need to define some way to keep up with log rotation.
</para>
</example>
</sect1>
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
