Database Reference
In-Depth Information
• Manipulating data that needs to be the same on all partitions. For ex-
ample, when you have a price list that other functions are using, then one
simple way to manage this price list is using a
RUN ON ALL
function.
SELECT and TARGET
The default behavior of a PL/Proxy function if no
SELECT
or
TARGET
is present is to
call the function with the exact same signature as itself in the remote partition.
Suppose we have the function:
CREATE OR REPLACE FUNCTION login(
IN i_username text, IN i_pwdhash text,
OUT status int, OUT message text )
AS $$
CONNECT 'dbname=chap9 host=10.10.10.1';
$$ LANGUAGE plproxy SECURITY DEFINER;
If it is defined in schema public, the following call
select * from login('bob',
'secret')
connects to the database
chap9
on host 10.10.10.1 and runs the fol-
lowing SQL statement there:
SELECT * FROM public.login('bob', 'secret')
This retrieves the result and returns it to its caller.
If you don't want to define a function inside the remote database, you can substitute
the default
select * from <thisfunction>(<arg1>, ...)
call with your
own by writing it in the function body of PL/Proxy function:
CREATE OR REPLACE FUNCTION
get_user_email(i_username text)
RETURNS SETOF text AS $$
CONNECT 'dbname=chap9 host=10.10.10.1';
SELECT email FROM user_info where username
