Database Reference
In-Depth Information
This example includes only the most basic options for the foreign table. By default, all
PostgreSQL foreign tables are editable/updatable, unless of course the remote account
you used doesn't have update access to that table. The
updatable
setting is a Boolean
setting that can be changed at the foreign table or the foreign server definition. For
example, to make your table read-only, execute:
ALTER
FOREIGN
TABLE
ft_facts
OPTIONS
(
ADD
updatable
'false'
);
You can set the table back to
updatable
by running:
ALTER
FOREIGN
TABLE
ft_facts
OPTIONS
(
SET
updatable
'true'
);
The
updatable
property at the table level overrides the foreign server setting.
In addition to changing
OPTIONS
, you can also add and drop columns with the
ALTER
FOREIGN TABLE
statement. The statement is covered in
PostgreSQL Manual ALTER
Querying Nonconventional Data Sources
The database world does not appear to be getting more homogeneous. Exotic databases
are sprouting up faster than we can keep tabs on. Some are fads and quickly drown in
their own hype. Some aspire to dethrone relational databases altogether. Some could
hardly be considered databases. The introduction of FDWs is in part a response to the
growing diversity. FDW assimilates without compromising the PosgreSQL core.
In this next example, we'll demonstrate how to use the
www_fdw
FDW to query web
services. We borrowed the example from
www_fdw Examples
.
The
www_fdw
FDW is not generally packaged with PostgreSQL. If you are on Linux/
Unix, it's an easy compile if you have the
postgresql-dev
package installed and can
download the latest source
.
We did the work of compiling for some Windows platforms;
you can download our binaries from
Windows-32 9.1 FDWs
and
Windows-64 9.3
FDWs
.
Now create an extension to hold the FDW:
CREATE
EXTENSION
www_fdw
;
Then create your Google foreign data server:
CREATE
SERVER
www_fdw_server_google_search
FOREIGN
DATA
WRAPPER
www_fdw
OPTIONS
(
uri
'http://ajax.googleapis.com/ajax/services/search/web?v=1.0'
);
The default format supported by
www_fdw
is JSON, so we didn't need to include it in the
OPTIONS
modifier. The other supported format is XML. For details on additional pa‐
and comes with its own API settings.