返回多个列的plpgsql函数被多次调用 [英] plpgsql function that returns multiple columns gets called multiple times

问题描述

我正在运行PostgreSQL 9.2.1，并具有一个返回3列的plpgsql函数.这样称呼(简化):

I'm running PostgreSQL 9.2.1 and have a plpgsql function that returns 3 columns. It's called like this (simplified):

SELECT (my_function(b.input)).*, a.other, b.columns
FROM table_a a
JOIN table_b b ON a.id = b.id
WHERE ...

该函数会打印出警告消息，但我惊讶地发现它被打印了3次.看来该函数被调用了3次-大概每列调用一次.这对性能不好！如何确保只调用一次?它已经标记为STABLE.

The function prints out a WARNING message and I was surprised to find it printed 3 times. It looks like the function gets called 3 times - presumably once for each column. This can't be good for performance! How can I make sure it's called only once? It's already marked STABLE.

如果我这样称呼

SELECT * FROM my_function(input)

然后警告仅打印一次，但是我不知道如何通过联接和返回的其他列将其集成到更大的查询中.也就是说，当我需要FROM列表中的其他表并且该函数从那些表中获取输入时，我不知道如何将该函数放入FROM列表中.

then the warning is printed only once, but I don't know how I can integrate that into the bigger query with the joins and other columns being returned. That is, I don't know how to put the function into the FROM list when I require other tables in the FROM list and the function takes its input from those.

修改:

查询(更接近原始查询):

The query (much closer to the original):

SELECT (my_aggregate_function(sub1.border, sub1.lower_limit, sub1.upper_limit, operation)).*
FROM
(
    SELECT (my_function(ca.timeslice_id)).*, agc.operation
    FROM geometry_component agc
    JOIN volume av ON agc.volume_id = av.id
    JOIN volume_dependency avd ON av.contributor_id = avd.id
    JOIN my_rowset_function('2013-02-22') ca ON avd.id = ca.feature_id
    WHERE agc.timeslice_id = 12345
    ORDER BY agc.sequence
) sub1

my_aggregate_function和my_function各自返回3列(边界，下限，上限)，但是my_aggregate_function是聚合，而my_function是常规函数.

my_aggregate_function and my_function each return 3 columns (border, lower_limit, upper_limit), but my_aggregate_function is an aggregate and my_function is a regular function.

推荐答案

这应该可以完成:

SELECT (y).*
FROM  (
   SELECT my_aggregate_function(border, lower_limit, upper_limit, operation) AS y
   FROM (
      SELECT (x).*, operation
      FROM  (
         SELECT my_function(ca.timeslice_id) AS x, agc.operation
         FROM   geometry_component agc
         JOIN   volume             av  ON av.id = agc.volume_id
         JOIN   volume_dependency  avd ON avd.id = av.contributor_id
         JOIN   my_rowset_function('2013-02-22') ca ON ca.feature_id = avd.id
         WHERE  agc.timeslice_id = 12345
         ORDER  BY agc.sequence
         ) sub1
      )sub2
   )sub3

这篇关于返回多个列的plpgsql函数被多次调用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！