PG获取存储过程及函数内容

**

PG获取存储过程及函数内容

**
函数:
SELECT ‘CREATE OR REPLACE FUNCTION xxx’||‘(’||chr(10)|| REPLACE(pg_catalog.pg_get_function_arguments(a.oid),‘,’,‘,’||chr(10))||chr(10)||‘)’
||chr(10)||'RETURNS ‘||
pg_catalog.pg_get_function_result(a.oid)||’ AS ‘||
a.prosrc ||’ ′ ∣ ∣ c h r ( 10 ) ∣ ∣ ′ L A N G U A G E p l p g s q l ′ F R O M p g c a t a l o g . p g p r o c a J O I N p g c a t a l o g . p g n a m e s p a c e b O N a . p r o n a m e s p a c e = b . o i d W H E R E p r o n a m e = ′ x x x ′ 存储过程 : S E L E C T ′ C R E A T E O R R E P L A C E P R O C E D U R E x x x ′ ∣ ∣ ′ ( ′ ∣ ∣ c h r ( 10 ) ∣ ∣ R E P L A C E ( C O A L E S C E ( p g c a t a l o g . p g g e t f u n c t i o n a r g u m e n t s ( a . o i d ) , ′ ′ ) , ′ , ′ , ′ , ′ ∣ ∣ c h r ( 10 ) ) ∣ ∣ c h r ( 10 ) ∣ ∣ ′ ) ′ ∣ ∣ c h r ( 10 ) ∣ ∣ C A S E W H E N C O A L E S C E ( p g c a t a l o g . p g g e t f u n c t i o n r e s u l t ( a . o i d ) , ′ ′ ) = ′ ′ T H E N ′ ′ E L S E ′ R E T U R N S ′ ∣ ∣ p g c a t a l o g . p g g e t f u n c t i o n r e s u l t ( a . o i d ) E N D ∣ ∣ ′ A S ′ ∣ ∣ a . p r o s r c ∣ ∣ ′ '|| chr(10)|| 'LANGUAGE plpgsql ' FROM pg_catalog.pg_proc a JOIN pg_catalog.pg_namespace b ON a.pronamespace = b.oid WHERE proname ='xxx' 存储过程: SELECT 'CREATE OR REPLACE PROCEDURE xxx '||'('||chr(10)|| REPLACE(COALESCE(pg_catalog.pg_get_function_arguments(a.oid),''),',',','||chr(10))||chr(10)||')' ||chr(10)||CASE WHEN COALESCE(pg_catalog.pg_get_function_result(a.oid),'')='' THEN '' ELSE 'RETURNS '||pg_catalog.pg_get_function_result(a.oid) END|| ' AS '|| a.prosrc ||' ∣∣chr(10)LANGUAGEplpgsqlFROMpgcatalog.pgprocaJOINpgcatalog.pgnamespacebONa.pronamespace=b.oidWHEREproname=xxx存储过程:SELECTCREATEORREPLACEPROCEDURExxx(∣∣chr(10)∣∣REPLACE(COALESCE(pgcatalog.pggetfunctionarguments(a.oid),′′),,,,∣∣chr(10))∣∣chr(10))∣∣chr(10)∣∣CASEWHENCOALESCE(pgcatalog.pggetfunctionresult(a.oid),′′)=′′THEN′′ELSERETURNS∣∣pgcatalog.pggetfunctionresult(a.oid)ENDAS∣∣a.prosrc '|| chr(10)|| 'LANGUAGE plpgsql ’
FROM pg_catalog.pg_proc a
JOIN pg_catalog.pg_namespace b ON a.pronamespace = b.oid
WHERE proname = ‘xxx’

  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值