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.

Rascality

Closely related question from yesterday: #14966208. Please provide your query, it should be possible to implement the subquery. – Sclaff

Thanks for the link, but I couldn't figure it out from that. I've added the query. – Rascality

Answer 1

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

Sclaff

Thank you, Erwin, you are the Jon Skeet of PostgreSQL questions! – Rascality

I wonder why PostgreSQL doesn't do it this way internally. Interestingly, the aggregate function got called only once (without the above workaround), even though it also returns multiple columns. At least my accumulator function got called only once per row. – Rascality

@EM: Oh, what an honor for Jon Skeet! ;) I'd call it a weakness of the query planner in the current version. The Postgres developers focused on getting LATERAL into version 9.3 which should provide more efficient ways for this kind of problems. – Sclaff

Answer 2

Unfortunately, that's a normal quirk of the implementation. It will be possible to avoid this problem when support for LATERAL queries goes in with 9.3.

For now I'm not aware of any good workaround.

Enterprising