Postgres 可以做到这一点吗？（特别是没有 function() 构造）？

发布于 2024-08-25 22:32:46 字数 690 浏览 7 评论 0原文

我有一些正在移植的 oracle 调用。今天我遇到了这段代码，它看起来像“过程”语言，但没有在函数或任何东西中声明......我的问题是：postgres 可以以这种形式处理这个吗？这需要采取什么形式？

DECLARE
BEGIN
   IF :start_time IS NULL OR
      :start_date IS NULL OR
      :end_time IS NULL OR
      :end_date IS NULL  THEN
         INSERT INTO ARPSPACE_AVAILABILITY
            (ARP_ARPSPACE_NM, ASA_TIME_ID, ASA_START_DT, ASA_END_DT)
         SELECT :arpspace_name,
                 1,
                 ASP.ASP_START_DT,
                 ASP.ASP_STOP_DT
           FROM ASP 
          WHERE EXISTS
             (SELECT ARP.ARP_ARPSPACE_NM
                FROM ARPSPACE ARP
               WHERE ARP.ARP_ARPSPACE_NM = :arpspace_name);
   END IF;
END;

原文

I have some oracle calls that I am porting. Today I came across this code which looks like "procedural" language, but is not declared in a function or anything... My question is: Can postgres handle this in this form? What form does this need to be in?

DECLARE
BEGIN
   IF :start_time IS NULL OR
      :start_date IS NULL OR
      :end_time IS NULL OR
      :end_date IS NULL  THEN
         INSERT INTO ARPSPACE_AVAILABILITY
            (ARP_ARPSPACE_NM, ASA_TIME_ID, ASA_START_DT, ASA_END_DT)
         SELECT :arpspace_name,
                 1,
                 ASP.ASP_START_DT,
                 ASP.ASP_STOP_DT
           FROM ASP 
          WHERE EXISTS
             (SELECT ARP.ARP_ARPSPACE_NM
                FROM ARPSPACE ARP
               WHERE ARP.ARP_ARPSPACE_NM = :arpspace_name);
   END IF;
END;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

成熟的代价 2024-09-01 22:32:46

不，还没有声明函数。在 9.0 版（很快将推出 Beta 版本）中，这将成为可能： http://developer .postgresql.org/pgdocs/postgres/sql-do.html

回复收藏 0 原文

来日方长 2024-09-01 22:32:46

简短的回答是肯定的，如果你可以声明一个函数。有关详细信息，请参阅手册中的 plpgsql 。

回复收藏 0 原文

不必你懂 2024-09-01 22:32:46

为什么不这样做（假设：标签是准备好的查询参数）？

     INSERT INTO ARPSPACE_AVAILABILITY
        (ARP_ARPSPACE_NM, ASA_TIME_ID, ASA_START_DT, ASA_END_DT)
     SELECT :arpspace_name,
             1,
             ASP.ASP_START_DT,
             ASP.ASP_STOP_DT
       FROM ASP 
      WHERE EXISTS
         (SELECT ARP.ARP_ARPSPACE_NM
            FROM ARPSPACE ARP
           WHERE ARP.ARP_ARPSPACE_NM = :arpspace_name)
      AND  (:start_time IS NULL OR
            :start_date IS NULL OR
            :end_time IS NULL OR
            :end_date IS NULL);

Why not do this (assuming :labels are prepared query parameters)?

     INSERT INTO ARPSPACE_AVAILABILITY
        (ARP_ARPSPACE_NM, ASA_TIME_ID, ASA_START_DT, ASA_END_DT)
     SELECT :arpspace_name,
             1,
             ASP.ASP_START_DT,
             ASP.ASP_STOP_DT
       FROM ASP 
      WHERE EXISTS
         (SELECT ARP.ARP_ARPSPACE_NM
            FROM ARPSPACE ARP
           WHERE ARP.ARP_ARPSPACE_NM = :arpspace_name)
      AND  (:start_time IS NULL OR
            :start_date IS NULL OR
            :end_time IS NULL OR
            :end_date IS NULL);

回复收藏 0 原文

~没有更多了~