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]
Posted by 54chen linux