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]

捐赠说明