[Postgre SQL] Procedure Example

select  beecloud.ag_recode_string_agg('CM','CONFIG')


-- DROP FUNCTION beecloud.ag_recode_string_agg(character varying, character varying);
create or replace function beecloud.ag_recode_string_agg(IN job_type_name character varying, IN db_instance_name character varying)
returns SETOF text AS
$BODY$
BEGIN
RETURN QUERY select string_agg(array_to_string(ARRAY[''||A.db_instance||''],','),',') as db_agg
                  from (select * from beecloud.agents where job_type=''||job_type_name||'' and db_instance=''||db_instance_name||'') A;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;



create or replace function beecloud.ag_recode_string_agg(IN job_type_name character varying)
returns SETOF text AS
$BODY$
BEGIN
RETURN QUERY select string_agg(array_to_string(ARRAY[''||A.db_instance||''],','),',') as db_agg
                  from (select * from beecloud.agents where job_type=''||job_type_name||'') A;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

-------------------------------------------------------------
select * from beecloud.agents
select beecloud.ag_create_connection_string('CM', 'CONFIG')


create or replace function beecloud.ag_create_connection_string(IN job_type character varying,IN db_instance_name character varying)
returns table(db_instance text,conn_string text) as
$BODY$
BEGIN
RETURN QUERY
    select A.db_instance ,'hostaddr='||A.db_server||' port='||A.db_port||' dbname='||A.db_database||' user='||A.db_account||' password='||A.db_pwd as conn_string
    from beecloud.ag_select_config_db(''||job_type||'',''||db_instance_name||'') A;
END;
$BODY$ LANGUAGE plpgsql VOLATILE;


-- - ----------------------------------------------------------
select * from beecloud.ag_recode_string_agg('CM');
select count(*) from unnest(dblink_get_connection()) as conn where conn='CONFIG';
select * from dblink_get_connections()
 select dblink_connect('CONFIG','hostaddr=... port=5432 dbname=postgres user=postgres password=...') 


select * from beecloud.ag_select_config_db('CM', 'CONFIG')

create or replace function beecloud.ag_select_config_db (IN job_type_name character varying,IN websv_schema character varying)
returns table(db_instance text,db_server text,db_port text,db_database text,db_account text,db_pwd text) as
$BODY$
declare
filter_str text;
v_sql text;
con_cnt int;
msg text;
BEGIN
filter_str:=''; msg :=''; con_cnt :=0;
    EXECUTE 'select * from beecloud.ag_recode_string_agg('''||job_type_name||''')' INTO filter_str;
   
    v_sql:='select db_instance,db_server,db_port,db_database,db_account,db_pwd from beecloud.database
    where db_instance in ('||filter_str||')';
    EXECUTE 'select count(*) from unnest(dblink_get_connections()) as conn where conn=''CONFIG'' ' INTO con_cnt;
   
    IF con_cnt=0 THEN
    EXECUTE ' select dblink_connect(''CONFIG'',''hostaddr=192.168.0.22 port=5432 dbname=postgres user=postgres password=beeHive0622'') ' INTO msg;
    ELSE
    EXECUTE 'select dblink_disconnect(''CONFIG'') ';
    EXECUTE ' select dblink_connect(''CONFIG'',''hostaddr=192.168.0.22 port=5432 dbname=postgres user=postgres password=beeHive0622'') ' INTO msg;
    END IF;
   
RETURN QUERY select * from dblink('CONFIG',''||v_sql||'') as
t(db_instance text,db_server text,db_port text,db_database text,db_account text,db_pwd text);
END;
$BODY$ LANGUAGE plpgsql VOLATILE;
-- ------------------------------------------

beecloud.ag_select_web_config_db();

create or replace function beecloud.ag_select_web_config_db(IN job_type_name character varying,IN instance_name character varying)
returns table (sv_ip text,sv_port text ,sv_user text,sv_pwd text,sv_rootpath text) as
$BODY$
declare filter_str text;conn_string text; v_sql text; v_sql_schema text; schema_nm text; con_cnt int; msg text;
BEGIN
    conn_string:='';
    filter_str:=''; msg:=''; con_cnt:=0;
    EXECUTE 'select * from beecloud.ag_recode_string_agg('''||job_type_name||''','''||instance_name||''') ' INTO filter_str;
    EXECUTE 'select count(*) from unnesst(dblink_get_connections()) as conn where conn=''CONFIG'' ' INTO con_cnt;

    IF con_cnt=0 THEN
    EXECUTE ' select dblink_connect(''CONFIG'',''hostaddr=... port=5432 dbname=postgres user=... password=...'') ' INTO msg;
    ELSE
    EXECUTE 'select dblink_disconnect(''CONFIG'')';
    EXECUTE ' select dblink_connect(''CONFIG'',''hostaddr=... port=5432 dbname=postgres user=... password=...'') ' INTO msg;
    END IF;
   
    v_sql_schema :='select db_schema from beecloud.database where db_instance='''||filter_str||''' ';
    EXECUTE 'select* from dblink(''CONFIG'','''||v_sql_schema||''') t(db_schema text)' INTO schema_nm;
    EXECUTE 'select A.conn_string from beecloud.ag_create_connection_string('''||job_type_name||''','''||instance_name||''','''||schema_name||''') A'
    INTO conn_string;
   
    EXECUTE ' select count(*) from unnest(dblink_get_connections()) as conn where conn='''||instance_name||''' ' INTO con_cnt;
   
    IF con_cnt=0 THEN
    EXECUTE 'select dblink_connect('''||instance_name||''','''||conn_string||''') ' INTO msg;
    ELSE
    EXECUTE 'select dblink_disconnect('''||instance_name||''') ' INTO msg;
    EXECUTE 'select dblink_connect('''||instance_name||''','''||conn_string||''') ' INTO msg;
    END IF;
   
    v_sql:='select sv_ip,sv_port,sv_user,sv_pwd,sv_rootpath from '||schema_nm||'.server where sv_type=''CHART'' ';
   
    RETURN QUERY select * from dblink(''||instance_name||'',''||v_sql||'')as
    t(sv_ip text,sv_port text ,sv_user text,sv_pwd text,sv_rootpath text);
END;
$BODY$ LANGUAGE plpgsql VOLATILE;