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
– Erwin Brandstetter, Commented Feb 21, 2013 at 12:15
Thanks for the link, but I couldn't figure it out from that. I've added the query. — EM0
– EM0, Commented Feb 22, 2013 at 12:45

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

2

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

answered Feb 22, 2013 at 13:13

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

EM0 Over a year ago

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

EM0 Over a year ago

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.

Erwin Brandstetter Over a year ago

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

Craig Ringer · Accepted Answer · 2013-02-21 09:58:24Z

1

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.

answered Feb 21, 2013 at 9:58

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Collectives™ on Stack Overflow

plpgsql function that returns multiple columns gets called multiple times

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related