Plproxy部署手册

安装过程如下:
1 安装plproxy:下载plproxy.安装完成plproxy后,运行{$PATH_TO_PG}/share/contrib/plproxy.sql
2 建立数据库_proxy;
3 建立plpgsql语言;
4 在_proxy中建立模式plproxy;分配权限 grant all on schema plproxy to <用户>。
5 建立pgcluster; 
 --以上步骤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;


原创文章如转载,请注明:转载自五四陈科学院[http://www.54chen.com]

捐款订阅54chen
捐赠说明