plproxy部署手册

 --以上步骤sql语句：
 CREATE FUNCTION plpgsql_call_handler () RETURNS language_handler AS
     '/usr/local/pgsql/lib/plpgsql.so'  LANGUAGE C;

 CREATE FUNCTION plpgsql_validator(oid) RETURNS void AS
     '/usr/local/pgsql/lib/plpgsql.so'  LANGUAGE C;

 CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
     HANDLER plpgsql_call_handler
     VALIDATOR plpgsql_validator;

 --------------------------------------------------------------------

 drop schema if exists plproxy cascade;

 create schema plproxy;

 grant all on schema plproxy to yahoo;

 --------------------------------------------------------------------
 create or replace function plproxy.get_cluster_version(cluster_name text)

 returns integer as $$

 begin
     if cluster_name = '<cluster名字>' then

 	return 8;

     end if;

     raise exception 'no such cluster: %', cluster_name;

 end; $$ language plpgsql;

 --------------------------------------------------------------------
 create or replace function plproxy.get_cluster_partitions(cluster_name text)

 returns setof text as $$

 begin

    if cluster_name = '<cluster名字>' then

 	--return next 'host=202.165.97.144 port=6000 user=chry password=chry dbname=chry_134';

 	return next 'host=pg1.sns.cn3.yahoo.com user=yahoo dbname=sns_search';
 	return next 'host=pg2.sns.cn3.yahoo.com user=yahoo dbname=sns_search';

 	return;

     end if;

     raise exception 'no such cluster: %', cluster_name;

 end; $$ language plpgsql;

 --------------------------------------------------------------------
 create or replace function plproxy.get_cluster_config(cluster_name text, out key text, out val text)

 returns setof record as $$

 begin

     key := 'statement_timeout';

     val := 60;

     return next;

     return;

 end; $$ language plpgsql;

6 建立查询函数 

 --在_proxy中建立查询函数:
 CREATE OR REPLACE FUNCTION public.doquery(query text)
 RETURNS setof record AS $$
 CLUSTER '<cluster名字>';
 RUN ON ALL;
 $$ LANGUAGE plproxy;

 --------------------------------------------------------------------
 CREATE OR REPLACE FUNCTION public.doquery(urlstr text,query text)
 RETURNS setof record AS $$
 CLUSTER '<cluster名字>';
 RUN ON hashtext(urlstr);
 $$ LANGUAGE plproxy;

 --------------------------------------------------------------------
 CREATE OR REPLACE FUNCTION public.dmlExec(query text)
 RETURNS integer AS $$
 CLUSTER '<cluster名字>';
 RUN ON ALL;
 $$ LANGUAGE plproxy;

 --------------------------------------------------------------------
 CREATE OR REPLACE FUNCTION public.dmlExec(urlstr text,query text)
 RETURNS integer AS $$
 CLUSTER '<cluster名字>';
 RUN ON hashtext(urlstr);
 $$ LANGUAGE plproxy;
 
 --在所联邦的数据库中建立查询函数:
 CREATE OR REPLACE FUNCTION public.doquery(query text)
 RETURNS SETOF RECORD AS $$
 DECLARE
 row RECORD;
 BEGIN
 for row in execute query loop
 return next row;
 end loop;
 return;
 END;
 $$ LANGUAGE plpgsql;

 ----------------------------------------------------------------
 CREATE OR REPLACE FUNCTION public.doquery(urlstr text,query text)
 RETURNS SETOF RECORD AS $$
 DECLARE
 row RECORD;
 BEGIN
 for row in execute query loop
 return next row;
 end loop;
 return;
 END;
 $$ LANGUAGE plpgsql;

 ----------------------------------------------------

 CREATE OR REPLACE FUNCTION public.dmlExec(query text)
 RETURNS integer AS $$
 DECLARE
 ret integer;
 BEGIN
 execute query;

 GET DIAGNOSTICS ret = ROW_COUNT;
 RETURN ret;

 END;
 $$ LANGUAGE plpgsql;

 ---------------------------------------------------------------
 CREATE OR REPLACE FUNCTION public.dmlExec(urlstr text,query text)
 RETURNS integer AS $$
 DECLARE
 ret integer;
 BEGIN
 execute query;

 GET DIAGNOSTICS ret = ROW_COUNT;
 RETURN ret;

 END;
 $$ LANGUAGE plpgsql;