plpgsql function that returns multiple columns gets called multiple times

Question

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 ...

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.

If I call it as

SELECT * FROM my_function(input)

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.

Edit:

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 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.

Closely related question from yesterday: stackoverflow.com/questions/14965708/…. Please provide your query, it should be possible to implement the subquery. — Erwin Brandstetter
Thanks for the link, but I couldn't figure it out from that. I've added the query. — EM0

Erwin Brandstetter Erwin Brandstetter · Accepted Answer · 2013-02-22T13:13:55

This should do the job:

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 function that returns multiple columns gets called multiple times

2 Answers