gpreport.html路径,GP通过存储过程实现过程代码导出到外部表

-- Function: test.p_getprosrc_branch_mid(character

varying)

-- DROP FUNCTION test.p_getprosrc_branch_mid(character

varying);

CREATE OR REPLACE FUNCTION test.p_getprosrc_branch(IN

v_schema_name character varying, OUT v_retcode character varying,

OUT v_retinfo character varying)

RETURNS record AS

$BODY$

DECLARE

R RECORD;--游标记录

V_SQL TEXT := ' ';--SQL语句

V_SQL_MID TEXT := ' ';--SQL中间语句

V_TABLENAME VARCHAR(300) := ' ';--外部表表名称

V_PROSRC TEXT := ' ';--存储过程过程代码

V_PRONAME VARCHAR(300) := ' ';--存储过程名称

V_PRODECLARE TEXT := ' ';--存储过程声明部分

V_COUNT_FLAG int:=0;--数据记录统计变量

V_PARAMETER_IO text := ' ';--参数IO类型

V_PARAMETER_TYPE text := ' ';--参数类型

V_PARAMETER_NAME text := ' ';--参数名称

BEGIN

raise notice '%','开始数据临时表状态检查！';

--创建用于存放临时数据的中间表

select count(1) into v_count_flag from pg_tables t where

t.tablename = 'getprosrc_branch';

IF V_COUNT_FLAG = 0 THEN

V_SQL := 'CREATE TABLE test.getprosrc_branch (prosrc

text,seq_num int)';

execute v_SQL;

V_SQL := ' ';

END IF;

raise notice '%','数据临时表创建完成！';

--获取模式信息并进行递归扫描

FOR R IN (SELECT distinct

PRONAME,'test.'||PRONAME||'_branch_w_external' AS

TABLENAME_MID,PROSRC AS PROSRC_MID

FROM PG_PROC WHERE pronamespace = (SELECT

oid FROM PG_NAMESPACE WHERE NSPNAME = lower(v_schema_name)) limit

1

) LOOP--获取存储过程并生成相关代码

V_TABLENAME := R.TABLENAME_MID;

V_PROSRC := R.PROSRC_MID;

V_PRONAME := R.PRONAME;

--查看对应外部表是否存在若存在则不做处理，若不存在则建表

SELECT COUNT(*) INTO V_COUNT_FLAG FROM PG_TABLES t WHERE

T.TABLENAME = LOWER(V_PRONAME||'_branch_w_external');

IF V_COUNT_FLAG >0 THEN

RAISE NOTICE '%','外部表'||V_TABLENAME||' 已经存在，继续！';

V_COUNT_FLAG := 0;

ELSE

--根据R结果集创建外部表

RAISE NOTICE '%','外部表不存在，创建外部表：'||V_TABLENAME;

V_SQL := 'CREATE WRITABLE EXTERNAL TABLE ' || V_TABLENAME || '

(' || 'PROSRC TEXT'|| ')

LOCATION (''GPFDIST://VM2:8081/' || 'PROBRANCH' || '/' ||

V_PRONAME||'.sql' ||''')

FORMAT ''TEXT'' (DELIMITER as '',''  NULL

as '''' escape ''OFF'')  ENCODING ''utf8''';--根据实际情况修改服务器地址

EXECUTE V_SQL;

V_SQL := ' ';--刷新建表语句

END IF;

--获取 存储过程声明及实现代码部分

RAISE NOTICE '%','数据导出:'||V_TABLENAME;

--获取函数参数个数

for i in 1 .. 20 loop

select proargnames[i] into v_sql_mid from pg_proc where

proname = V_PRONAME;

if v_sql_mid is not null then

v_count_flag := i;

v_sql_mid := ' ';

end if;

end loop;

raise notice '%','参数个数:'||v_count_flag||'

!';

--获取函数声明部分

--获取参数声明部分

v_sql_mid := ' ';

for i in 1 .. v_count_flag loop

select proargmodes[i] into v_sql_mid from pg_proc where

proname =  v_proname;

if v_sql_mid is null then

raise notice '%','存储过程或函数未标记参数类型为 in 或 out 文本输出后统一默认为 in

类型';

v_sql_mid := ' ';

select( case p.proargmodes[i] when  'i' then

'in'

when 'o' then 'out'

when null then 'in'

else 'in' end )||' '||p.proargnames[i]||' '||t.typname||','

INTO V_SQL_MID

from pg_proc p, pg_type t

where p.proname = v_proname and P.proargtypes[i-1] =

t.oid;

V_SQL := V_SQL||V_SQL_MID;

v_sql_mid := ' ';

else

raise notice '%','存储过程或函数参数类型正常';

v_sql_mid := ' ';

select( case p.proargmodes[i] when  'i' then

'in'

when 'o' then 'out'

when null then 'in'

else 'in' end )||' '||p.proargnames[i]||' '||t.typname||','

INTO V_SQL_MID

from pg_proc p, pg_type t

where p.proname = v_proname and P.proallargtypes[i] =

t.oid;

V_SQL := V_SQL||V_SQL_MID;

v_sql_mid := ' ';

end if;

end loop;

raise notice '%','参数声明部分获取完毕！';

V_SQL := 'create or replace function

'||v_schema_name||'.'||V_PRONAME||'

('||substr(V_SQL,1,length(V_SQL)-1)||') RETURNS ';

V_SQL_MID := ' ';

--获取返回值声明部分

select t.typname::text into V_SQL_MID

from pg_proc p, pg_type t

where p.proname = v_proname and P.prorettype = t.oid;

V_SQL := V_SQL ||V_SQL_MID||' AS '||'$'||'BODY'||'$ ';

--函数结尾声明部分

V_SQL_MID := ' ';

V_SQL_MID := '$'||'BODY'||'$'||'  LANGUAGE

'||'''plpgsql'''||' VOLATILE;';

raise notice '%','结尾部分获取完毕！';

insert into test.getprosrc_branch(seq_num,prosrc) values

(1,V_SQL);--function声明部分

INSERT INTO test.getprosrc_branch(seq_num,prosrc) values

(2,V_PROSRC);--function实现代码部分

INSERT INTO test.getprosrc_branch(seq_num,prosrc) values

(3,V_SQL_mid);--function结束部分

raise notice '%','临时数据插入！';

V_SQL:= 'insert into '||V_TABLENAME||' (select prosrc from

test.getprosrc_branch where seq_num = 1)' ;

execute v_sql;

V_SQL:= 'insert into '||V_TABLENAME||' (select prosrc from

test.getprosrc_branch where seq_num = 2)' ;

execute v_sql;

V_SQL:= 'insert into '||V_TABLENAME||' (select prosrc from

test.getprosrc_branch where seq_num = 3)' ;

execute v_sql;

raise notice '%','脚本生成完毕！';

--清理临时数据

V_SQL := 'delete from test.getprosrc_branch';

execute v_sql;

raise notice '%','临时数据清理完毕！';

END LOOP;

V_RETCODE := 'SUCCESS';

V_RETINFO := '结束';

EXCEPTION WHEN OTHERS THEN

V_RETCODE := 'FAIL';

RAISE NOTICE 'ERROR INFORMATION IS %,%', V_RETINFO,

V_RETCODE;

END;

$BODY$

LANGUAGE 'plpgsql' VOLATILE;