坚信科学,分享技术

2018全新版本,未来在blog.54chen.com更新博客!

>>>尝试更加利于阅读的2014版科学院,以后都在新版上写。

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]
本文链接: http://www.54chen.com/_linux_/plproxy%e9%83%a8%e7%bd%b2%e6%89%8b%e5%86%8c.html

This entry was posted in linux and tagged , . Bookmark the permalink.

1 Response » to “plproxy部署手册”

Leave a Reply