將數組從一個 plpgsql 函式傳遞到另一個

我在PostgreSQL 9.4中有一個呼叫另一個函式的函式。兩者都是用 plpgsql 編寫的。子函式從用於創建新表的表中獲取一個表示 ID 的數組參數。

父函式：

CREATE OR REPLACE FUNCTION foo(linktable REGCLASS, 
                              inttable REGCLASS, 
                              verttable REGCLASS)

DECLARE
   intersection_ids INT[];

-- ... SNIP

   BEGIN
       EXECUTE format('SELECT array_agg(id) from %s',inttable) INTO intersection_ids;
       EXECUTE format('
           SELECT tdgSetTurnInfo(%L,%L,%L,%L);
           ',  linktable,
               inttable,
               verttable,
               intersection_ids);
   END;

-- SNIP ...

子函式：

CREATE OR REPLACE FUNCTION bar(linktable REGCLASS,
                              inttable REGCLASS,
                              verttable REGCLASS,
                              intersection_ids INT[])

-- ... SNIP

   EXECUTE format('
       INSERT INTO %s (int_id,
                       ref_link_id,
                       match_link_id,
                       ref_azimuth,
                       match_azimuth)
       SELECT  int.id,
               l1.id,
               l2.id,
               degrees(ST_Azimuth(ST_StartPoint(l1.geom),ST_EndPoint(l1.geom))),
               degrees(ST_Azimuth(ST_StartPoint(l2.geom),ST_EndPoint(l2.geom)))
       FROM    %s int,
               %s v1,
               %s v2,
               %s l1,
               %s l2
       WHERE   int.id ANY %L
       AND     int.id = v1.intersection_id
       AND     int.id = v2.intersection_id
       AND     l1.target_node = v1.node_id
       AND     l2.source_node = v2.node_id
       AND     l1.road_id IS NOT NULL
       AND     l2.road_id IS NOT NULL
       AND     l1.road_id != l2.road_id;
       ',  temptable,
           inttable,
           verttable,
           verttable,
           linktable,
           linktable,
           intersection_ids);

-- SNIP ...

當我執行此程式碼時，出現錯誤：

詢問：

INSERT INTO tdggtitemptbl (int_id,
               ref_link_id,
               match_link_id,
               ref_azimuth,
               match_azimuth)
SELECT  int.id,
       l1.id,
       l2.id,
       degrees(ST_Azimuth(ST_StartPoint(l1.geom),ST_EndPoint(l1.geom))),
       degrees(ST_Azimuth(ST_StartPoint(l2.geom),ST_EndPoint(l2.geom)))
FROM    a_intersections int,
       a_net_vert v1,
       a_net_vert v2,
       a_net_link l1,
       a_net_link l2
WHERE   int.id ANY '{1,2,3,4,5,6,7,8,9,10,11,12,13}'
AND     int.id = v1.intersection_id
AND     int.id = v2.intersection_id
AND     l1.target_node = v1.node_id
AND     l2.source_node = v2.node_id
AND     l1.road_id IS NOT NULL
AND     l2.road_id IS NOT NULL
AND     l1.road_id != l2.road_id;

但是，當我在函式之外執行類似的程式碼並且沒有format()語句時，它執行沒有問題。INT[]通過 format() 執行我的數組有問題嗎？還是完全是別的東西？

您可以通過使用該子句將值作為值傳遞來避免各種複雜情況：USING

CREATE OR REPLACE FUNCTION foo(linktable regclass, 
                              inttable  regclass, 
                              verttable regclass)
RETURNS void AS
$func$
BEGIN
  EXECUTE format(
     'SELECT tdgSetTurnInfo($1, $2, $3, array_agg(t.id)) FROM %s t'
    , linktable)
  USING  $1, $2, $3;  -- you could use parameter names as well
END
$func$ LANGUAGE plpgsql;

並將生成的數組直接傳遞給單個SELECT.

請注意，$1, $2, $3在EXECUTE引用USING子句中，並且恰好與USING $1, $2, $3. 相同的符號，獨立的範圍！

修復錯誤的直接原因後（如@Craig 評論）：

WHERE int.id ANY %L

WHERE   int.id = ANY (%L)

以類似的方式簡化第二個函式：

CREATE OR REPLACE FUNCTION bar(linktable regclass,
                              inttable  regclass,
                              verttable regclass,
                              intersection_ids int[])

   -- ... SNIP

   EXECUTE format('
       INSERT INTO %1$s
              (int_id, ref_link_id, match_link_id, ref_azimuth, match_azimuth)
       SELECT  int.id, l1.id      , l2.id
             , degrees(ST_Azimuth(ST_StartPoint(l1.geom),ST_EndPoint(l1.geom)))
               degrees(ST_Azimuth(ST_StartPoint(l2.geom),ST_EndPoint(l2.geom)))
       FROM    %2$s int
       JOIN    %3$s v1 ON v1.intersection_id = int.id
       JOIN    %3$s v2 ON v2.intersection_id = int.id
       JOIN    %4$s l1 ON l1.target_node = v1.node_id
       JOIN    %4$s l2 ON l2.source_node = v2.node_id
                    AND l2.road_id <> l1.road_id
       WHERE   int.id = ANY ($1)    -- fix syntax and pass as value!
       AND     l1.road_id IS NOT NULL
       AND     l2.road_id IS NOT NULL
       ', temptable,
          inttable,
          verttable,
          linktable)
   USING intersection_ids;

   ...

整個設置可能會進一步簡化。

引用自：https://dba.stackexchange.com/questions/112000