Database Reference
In-Depth Information
You need a local function, but you don't need to call a remote function; you can use SQL
statements directly. The following example shows a parameterized function:
CREATE FUNCTION get_cust_email(p_username text)
RETURNS SETOF text AS $$
CONNECT 'dbname=myremoteserver';
SELECT email FROM users WHERE username = p_username;
$$ LANGUAGE plproxy;
PL/Proxy is specifically designed to allow more complex architectures for sharding and load
balancing. The
RUN
ON
command allows us to specify the remote database dynamically on
which we will run the SQL statement. So the preceding example becomes like the following:
CREATE FUNCTION get_cust_email(p_username text)
RETURNS SETOF text AS $$
CLUSTER 'mycluster';
RUN ON hashtext(p_username);
SELECT email FROM users WHERE username = p_username;
$$ LANGUAGE plproxy;
You'll likely need to read the chapter on
Replication
also before you begin designing
application architectures using these concepts.
Making views updateable
PostgreSQL supports the SQL Standard command
CREATE
VIEW
, though the views it creates
are not automatically updateable. This could change in later releases, but at 9.0, that difficulty
still exists for the administrator to overcome. We discuss those issues here.
Getting ready
First, you need to consider that only simple views can be made to receive inserts, updates,
and deletes easily. The SQL Standard differentiates between views that are "simple
updateable" and more complex views that could not be expected to be updateable.
So before we proceed, we need to check the understanding of what is a simply updateable
view and what is not. Starting from the
cust
table as follows:
postgres=# SELECT * FROM cust;
customerid | firstname | lastname | age
------------+-----------+----------+-----
1 | Philip | Marlowe | 38
2 | Richard | Hannay | 42
3 | Holly | Martins | 25
4 | Harry | Palmer | 36
4 | Mark | Hall | 47
(5 rows)
