删除存储过程的语句

PostgreSQL如何删除存储过程呢，当使用dbeaver删除过程的时候日志中可以看到记录：

2023-09-11 14:49:37.267939T,PostgreSQL JDBC Driver,lightdb,hsfut,10.20.31.207(48542),client backend,CALL,00000,2023-09-11 14:49:13 CST,0,38070,LOG:  execute <unnamed>: CALL HYA_CBANK.P_ZZZF_WTZLCL($1, $2, $3, $4, $5, $6)
2023-09-11 14:49:37.267939T,PostgreSQL JDBC Driver,lightdb,hsfut,10.20.31.207(48542),client backend,CALL,00000,2023-09-11 14:49:13 CST,0,38070,DETAIL:  parameters: $1 = NULL, $2 = NULL, $3 = NULL, $4 = '0', $5 = '100', $6 = '*'
2023-09-11 14:49:37.271113T,PostgreSQL JDBC Driver,lightdb,hsfut,10.20.31.207(48542),client backend,SELECT,00000,2023-09-11 14:49:13 CST,0,38070,LOG:  execute <unnamed>: select 1 from dual
2023-09-11 14:49:38.349860T,DBeaver 23.0.4 - Main <hsfut>,lightdb,hsfut,10.188.123.177(52296),client backend,BEGIN,00000,2023-09-11 14:40:33 CST,0,53263,LOG:  execute <unnamed>: BEGIN
2023-09-11 14:49:38.349923T,DBeaver 23.0.4 - Main <hsfut>,lightdb,hsfut,10.188.123.177(52296),client backend,DROP PROCEDURE,00000,2023-09-11 14:40:33 CST,0,53263,LOG:  execute <unnamed>: DROP PROCEDURE hs_futures.p_lc_auto_flowend(inout numeric, inout varchar, inout oracle.sys_refcursor, in oracle."varchar2")
2023-09-11 14:49:38.355287T,DBeaver 23.0.4 - Main <hsfut>,lightdb,hsfut,10.188.123.177(52296),client backend,COMMIT,00000,2023-09-11 14:40:33 CST,12656878,53263,LOG:  execute <unnamed>: COMMIT
2023-09-11 14:49:38.362266T,DBeaver 23.0.4 - Main <hsfut>,lightdb,hsfut,10.188.123.177(52296),client backend,BEGIN,00000,2023-09-11 14:40:33 CST,0,53263,LOG:  execute <unnamed>: BEGIN
2023-09-11 14:49:38.362336T,DBeaver 23.0.4 - Main <hsfut>,lightdb,hsfut,10.188.123.177(52296),client backend,DROP PROCEDURE,00000,2023-09-11 14:40:33 CST,0,53263,LOG:  execute <unnamed>: DROP PROCEDURE hs_futures.p_lc_auto_flowend(inout numeric, inout varchar, inout refcursor, in varchar)
2023-09-11 14:49:38.368308T,DBeaver 23.0.4 - Main <hsfut>,lightdb,hsfut,10.188.123.177(52296),client backend,COMMIT,00000,2023-09-11 14:40:33 CST,12656879,53263,LOG:  execute <unnamed>: COMMIT

可以看到命令为：

DROP PROCEDURE hs_futures.p_lc_auto_flowend(inout numeric, inout varchar, inout oracle.sys_refcursor, in oracle."varchar2")

找到存储过程定义，我们可以通过下面获得

select * from 
(select 'drop procedure ' || n.nspname ||'.'|| p.proname ||' (' ||
  pg_catalog.pg_get_function_arguments(p.oid) || ');' as drop_procedure_ddl,
  count(*) over (partition by n.nspname ||'.'|| p.proname order by n.nspname, p.proname) as cnt,
  row_number() over (partition by n.nspname ||'.'|| p.proname order by n.nspname, p.proname) as rn
 --pg_catalog.pg_get_function_result(p.oid) as "Result data type",
 --pg_get_functiondef(p.oid) as definition
 --CASE
 -- WHEN p.proisagg THEN 'agg'
 -- WHEN p.proiswindow THEN 'window'
 -- WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'
 -- ELSE 'normal'
 --END as "Type"
FROM pg_catalog.pg_proc p
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE pg_catalog.pg_function_is_visible(p.oid)
      AND n.nspname like 'hs_%'
      AND n.nspname <> 'information_schema'
      -- AND pg_catalog.pg_get_function_arguments(p.oid) not like '%varchar2%'
) x where x.cnt <> 1 
and x.rn = 1

  
drop procedure hs_finance.p_cl_cw_info_pzxxcl_main (INOUT an_o_ret_code numeric, INOUT ac_o_ret_msg character varying, INOUT ac_o_is_mzt character, INOUT ac_o_ret_cur refcursor, ac_i_staffid character varying, ac_i_gszt character varying, an_i_ztid numeric, ac_i_pzfzid character varying, ac_i_ksrq character, ac_i_jsrq character, an_i_pzid numeric);

参考：
https://stackoverflow.com/questions/1347282/how-can-i-get-a-list-of-all-functions-stored-in-the-database-of-a-particular-schhttps://stackoverflow.com/questions/3524859/how-to-display-full-stored-procedure-code