时间戳

首页 > 解决方案 > 无法获取 SP 的动态执行以返回 INOUT 参数

postgresql - 无法获取 SP 的动态执行以返回 INOUT 参数

问题描述

使用 PostgreSQL 13.2，其中存储过程（请求者）被赋予要运行的存储过程列表的名称（作业组）。所有以这种方式执行的 sp 都被编码为写入日志记录作为它们的最后一个任务。我选择从所有 sp 中提取“附加日志”代码，而是使用 INOUT 行类型参数发回日志记录（总是一条记录），但遇到了麻烦。在下面的示例中，请求者 sp 将从它调用的 sp 返回的记录加载到形状类似于永久日志表的临时表中。

该永久表如下所示：

create table public.job_log (
    log_id        integer,
    event_id      integer,
    job_id        integer,
    rows_affected integer);

请求者 sp 执行的任何一项作业都可能如下所示：

CREATE OR REPLACE procedure public.get_log_rcd(
    inout p_log_rcd public.job_log)
    LANGUAGE 'plpgsql'
as
$BODY$
declare
    v_log_id         integer     = 40;
    v_event_id       integer     = 698;
    v_job_id         integer     = 45;
    v_rows_affected  integer     = 60;
begin

    select 
         v_log_id   
       , v_event_id  
       , v_job_id       
       , v_rows_affected
      into
        p_log_rcd.log_id,
        p_log_rcd.event_id,
        p_log_rcd.job_id,
        p_log_rcd.rows_affected;

end;
$BODY$

这个示例 sp 不做任何事情——这里的目的只是模拟日志参数的初始化以返回给调用者。

同样，将运行上述作业的请求者 sp 创建一个与永久日志具有相同结构的临时表：

drop table if exists tmp_log_cache;
create temp table tmp_log_cache as table public.job_log with no data;

如果请求者 sp 不必执行动态 SQL，它看起来像这里的块：

do
$$
declare
    big_local public.job_log;
begin
    
    call public.get_log_rcd( big_local );
      
    insert into tmp_log_cache (
         log_id       
       , event_id     
       , job_id       
       , rows_affected )
    values ( 
         big_local.log_id
       , big_local.event_id
       , big_local.job_id
       , big_local.rows_affected);     
      
end;
$$;

做一个

select * from tmp_log_cache;

返回包含预期的 4 列值的行，一切正常。但是，需要动态执行。而且，我相信这里的大多数人都知道，以下狗不会打猎：

do
$$
declare
    big_local public.job_log;
    v_query_text varchar;
    v_job_name varchar = 'public.get_log_rcd';
begin
    
    select 'call ' || v_job_name || '( $1 );'
      into v_query_text;
   execute v_query_text  using big_local::public.job_log;
  
    insert into tmp_log_cache (
         log_id       
       , event_id     
       , job_id       
       , rows_affected )
    values ( 
         big_local.log_id
       , big_local.event_id
       , big_local.job_id
       , big_local.rows_affected);    
      
end;
$$;

上面的动态语句执行没有错误，但插入语句只有 NULL 值可以使用——插入一行，全为空。任何建议都热烈欢迎。组成各种工作组的 sp 可能已经被实现为函数，尽管在所有情况下，它们的主要任务是按摩、规范化、清理遥测数据，而不是吐出任何东西。

标签： postgresql

解决方案

谢谢——我不会考虑使用执行来执行“做”的能力。我只是没想到。好吧，这是我的解决方案：翻转到函数。

了解到我的“请求者”只允许运行 sp，因为那是我们必须使用 SQL Server 做的事情并且它是反射，我做了 1 行更改，需要将上面的示例 sp 翻转为一个函数：

CREATE OR REPLACE function public.get_log_rcdf(
    inout p_log_rcd public.job_log)
    returns public.job_log
    LANGUAGE 'plpgsql'
as
$BODY$
declare
    v_log_id         integer     = 40;
    v_event_id       integer     = 698;
    v_job_id         integer     = 45;
    v_rows_affected  integer     = 60;
begin

    select 
         v_log_id   
       , v_event_id  
       , v_job_id       
       , v_rows_affected
      into
        p_log_rcd.log_id,
        p_log_rcd.event_id,
        p_log_rcd.job_id,
        p_log_rcd.rows_affected;

end;
$BODY$

事实上，对函数的更改需要添加一个 RETURNS 行。完毕。然后，将动态调用调整为 SELECT，并使用 INTO 修改执行：

do
$$
declare
    big_local public.job_log;
    v_query_text varchar;
    v_job_name varchar = 'public.get_log_rcdf';
begin
    
    select 'select * from ' || v_job_name || '( $1 );'
      into v_query_text;
    raise info 'SQL text is: %', v_query_text;    
   execute v_query_text into big_local using big_local;
  
    insert into tmp_log_cache (
         log_id       
       , event_id     
       , job_id       
       , rows_affected )
    values ( 
         big_local.log_id
       , big_local.event_id
       , big_local.job_id
       , big_local.rows_affected);    
      
end;
$$;

并且该过程现在完全按照需要进行。如第一个答案所示，我整理了对动态函数名称的处理，我想我们已经完成了。

推荐阅读