Cursor/Dynamic SQL执行Postgres存储过程

huangapple
go评论82阅读模式
英文:

Cursor/Dynamic SQL to execute Postgres Procedure

问题

Here's the translated portion of your text:

我有一个从表中聚合一些数据并将结果放入不同表格以在PowerBI报告中显示的Postgres存储过程。该存储过程每天聚合数据，因此接受“startDate”和“endDate”参数（其中“endDate”始终为“startDate + 1”）。

  1. CREATE OR REPLACE PROCEDURE schema.DailyCalculation(
  2.     "startDate" timestamp without time zone,
  3.     "endDate" timestamp without time zone)
  4. AS $$
  5. DECLARE
  6.    -- 一些声明
  7. BEGIN
  8.    -- 做一些事情
  9. END;
  10. $$ LANGUAGE plpgsql;

我现在发现了过去计算数据的错误，并希望为日期范围调用此存储过程。为了将其分成可管理的块，我仍然希望为单一日期范围调用“DailyCalculation”，但创建一个新的存储过程，我可以为其提供更长的日期范围，该存储过程将反复调用“DailyCalculation”，只使用日期范围内的单一日期范围。

举个例子，我想重新计算2023年1月。所以，我想调用类似以下的东西：

  1. CALL schema.RecalculateDailyData('2023-01-01', '2023-01-31');

我希望有效执行以下操作：

  1. CALL schema.DailyCalculation('2023-01-01', '2023-01-02');
  2. CALL schema.DailyCalculation('2023-01-02', '2023-01-03');
  3. CALL schema.DailyCalculation('2023-01-03', '2023-01-04');
  4. ...
  5. CALL schema.DailyCalculation('2023-01-30', '2023-01-31');

我已经能够生成一个查询，可以生成动态SQL以实现此效果：

  1. SELECT CONCAT('CALL schema.DailyCalculation(''', '', date_trunc('day', dd)::date, '', '', date_trunc('day', dd)::date + 1, '', '');') FROM generate_series( '2023-01-01'::timestamp, '2023-01-31'::timestamp, '1 day'::interval) dd;

如何遍历此结果集并执行动态SQL查询？

英文:

I have a Postgres procedure that aggregates some data from a table and puts the results in a different table for display on a PowerBI report. The procedure aggregates the data on a daily basis, so accepts a startDate and endDate parameter (where endDate is always startDate + 1).

  1. CREATE OR REPLACE PROCEDURE schema.DailyCalculation(
  2.     "startDate" timestamp without time zone,
  3.     "endDate" timestamp without time zone)
  4. AS $$
  5. DECLARE
  6.    -- some declarations 
  7. BEGIN
  8.    -- do some stuff
  9. END;
  10. $$ LANGUAGE plpgsql;

I've now identified an error in the way we calculated data in the past, and want to call this procedure for a date range. To break it into manageable chunks, I still want to call DailyCalculation for a single day period, but create a new procedure that I can provide with a longer date range that will repeatedly call DailyCalculation with only single day ranges in that date range.

For arguments' sake, I want to recalculate January 2023. So, I want to call something like:

  1. CALL schema.RecalculateDailyData('2023-01-01', '2023-01-31');

which I want to effectively do the following:

  1. CALL schema.DailyCalculation('2023-01-01', '2023-01-02');
  2. CALL schema.DailyCalculation('2023-01-02', '2023-01-03');
  3. CALL schema.DailyCalculation('2023-01-03', '2023-01-04');
  4. ...
  5. CALL schema.DailyCalculation('2023-01-30', '2023-01-31');

I've been able to generate a query that would generate dynamic SQL to this effect:

  1. SELECT CONCAT('CALL schema.DailyCalculation(''', date_trunc('day', dd)::date, ''', ''', date_trunc('day', dd)::date + 1, ''');') FROM generate_series( '2023-01-01'::timestamp, '2023-01-31'::timestamp, '1 day'::interval) dd;

How do I iterate through this resultset, and execute the dynamic SQL query?

答案1

得分: 1

您可以使用函数 generate_series() 来创建感兴趣的每个日期，并在 for 循环中调用您的存储过程。

  1. create or replace procedure DailyCalculation(
  2.                   startdate  timestamp without time zone 
  3.                 , enddate    timestamp without time zone
  4.                 )
  5.    language plpgsql
  6. as $$
  7. declare
  8.    -- 一些声明
  9. begin
  10.    insert into demo(routine_name, start_date, end_date)
  11.         values ('Procedure:DailyCalculations', startdate, enddate); 
  12. end;
  13. $$;
  15. -- DO block 来调用 DailyCalculations
  16. do $$
  17. declare 
  18.     sdt record; 
  19. begin
  20.     for sdt in (select dt 
  21.                   from generate_series ('2023-01-01'::timestamp
  22.                                        , '2023-01-31'::timestamp
  23.                                        , interval '1 day' 
  24.                                        ) gs(dt)
  25.                )
  26.     loop 
  27.         call DailyCalculation(sdt.dt, sdt.dt+interval '1 day');
  28.     end loop;
  29. end;
  30. $$;

或者，您可以创建一个函数（返回 Null），而不是存储过程，并通过单个查询完成此操作。

  1. create or replace function DailyCalculation_fn(
  2.                   startdate  timestamp without time zone 
  3.                 , enddate    timestamp without time zone
  4.                 )
  5.     returns void    
  6.    language plpgsql 
  7. as $$
  8. declare
  9.    -- 一些声明 
  10. begin
  11.    insert into demo(routine_name, start_date, end_date)
  12.         values ('Function DailyCalculation_fn', startdate, enddate); 
  13. end;
  14. $$; 
  16. select DailyCalculation_fn(dt, dt+interval '1 day') 
  17.   from generate_series ('2023-01-01'::timestamp
  18.                        , '2023-01-31'::timestamp
  19.                        , interval '1 day' 
  20.                        ) gs(dt);

然而，备选函数有时依赖副作用，因此有时被认为是不合适的原因。您可以在这里看到每个示例。对于两者都只是插入到一个示例表中。

注意：您指定了存储过程需要两个参数，startDate 和 endDate，但然后声明结束日期始终是开始日期+1。如果这是必需的，请为什么传递 enddate，只需让您的存储过程/函数计算结束日期。这样，您可以确保结束日期始终正确。当传递 startDate+2 或 startDate+10 或其他不等于 startDate+1 的值时，您的存储过程会执行什么操作？
1: https://dbfiddle.uk/JnWoOyBN

英文:

You can use the function generate_series() to create each date of interested and call your procedure in a for loop.

  1. create or replace procedure DailyCalculation(
  2.                   startdate  timestamp without time zone 
  3.                 , enddate    timestamp without time zone
  4.                 )
  5.    language plpgsql
  6. as $$
  7. declare
  8.    -- some declarations 
  9. begin
  10.    insert into demo(routine_name, start_date, end_date)
  11.         values ('Procedure:DailyCalculations', startdate,enddate); 
  12. end;
  13. $$;
  15. -- DO block to call DailyCalculations
  16. do $$
  17. declare 
  18.     sdt record; 
  19. begin
  20.     for sdt in (select dt 
  21.                   from generate_series ( '2023-01-01'::timestamp
  22.                                        , '2023-01-31'::timestamp
  23.                                        , interval '1 day' 
  24.                                        ) gs(dt)
  25.                )
  26.     loop 
  27.         call DailyCalculation(sdt.dt, sdt.dt+interval '1 day');
  28.     end loop;
  29. end;
  30. $$;

Alternatively you can create a Function (returns Null) instead of a Procedure and accomplish this with a single query.

  1. create or replace function DailyCalculation_fn(
  2.                   startdate  timestamp without time zone 
  3.                 , enddate    timestamp without time zone
  4.                 )
  5.     returns void    
  6.    language plpgsql 
  7. as $$
  8. declare
  9.    -- some declarations 
  10. begin
  11.    insert into demo(routine_name, start_date, end_date)
  12.         values ('Function DailyCalculation_fn', startdate,enddate); 
  13. end;
  14. $$; 
  16. select DailyCalculation_fn(dt, dt+interval '1 day') 
  17.   from generate_series ('2023-01-01'::timestamp
  18.                        , '2023-01-31'::timestamp
  19.                        , interval '1 day' 
  20.                        ) gs(dt);

The alternative function however does rely side effects and is sometimes considered inappropriate for that reason.

See demo for each here. For demo both just insert into a demo table.

NOTE: You specified tour procedure needs 2 parameters, startDate and endDate, but then declare that end date is always be start+1. If this is required, why pass enddate just let your procedure/function calculate the end date. That way you ensure the end date is always correct. What does your Procedure do when startDate+2 or startDate+10 or anything other than startdate+1 is passed.

huangapple
  • 本文由 发表于 2023年5月17日 15:53:24
  • 转载请务必保留本文链接：https://go.coder-hub.com/76269729.html
  • postgresql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定