Skip to content
Advertisement

How to avoid multiple function evals with the (func()).* syntax in a query?

Context

When a function returns a TABLE or a SETOF composite-type, like this one:

the results can be accessed by various methods:

  1. select * from func(3) will produce these output columns :
  1. select func(3) will produce only one output column of ROW type.
  1. select (func(3)).* will produce like #1:

When the function argument comes from a table or a subquery, the syntax #3 is the only possible one, as in:

or as in this related answer. If we had LATERAL JOIN we could use that, but until PostgreSQL 9.3 is out, it’s not supported, and the previous versions will still be used for years anyway.

Problem

The problem with syntax #3 is that the function is called as many times as there are columns in the result. There’s no apparent reason for that, but it happens.
We can see it in version 9.2 by adding a RAISE NOTICE 'called for %', n in the function. With the query above, it outputs:

Now, if the function is changed to return 4 columns, like this:

then the same query outputs:

2 function calls were needed, 8 were actually made. The ratio is the number of output columns.

With syntax #2 that produces the same result except for the output columns layout, these multiple calls don’t happen:

gives:

followed by the 6 resulting rows:

Questions

Is there a syntax or a construct with 9.2 that would achieve the expected result by doing only the minimum required function calls?

Bonus question: why do the multiple evaluations happen at all?

Advertisement

Answer

You can wrap it up in a subquery but that’s not guaranteed safe without the OFFSET 0 hack. In 9.3, use LATERAL. The problem is caused by the parser effectively macro-expanding * into a column list.

Workaround

Where:

will evaluate my_func n times for n result columns from the function, this formulation:

generally will not, and tends not to add an additional scan at runtime. To guarantee that multiple evaluation won’t be performed you can use the OFFSET 0 hack or abuse PostgreSQL’s failure to optimise across CTE boundaries:

or:

In PostgreSQL 9.3 you can use LATERAL to get a saner behaviour:

LEFT JOIN LATERAL ... ON true retains all rows like the original query, even if the function call returns no row.

Demo

Create a function that isn’t inlineable as a demonstration:

and a table of dummy data:

then try the above versions. You’ll see that the first raises three notices per invocation; the latter only raise one.

Why?

Good question. It’s horrible.

It looks like:

is expanded as:

in parsing, according to a look at debug_print_parse, debug_print_rewritten and debug_print_plan. The (trimmed) parse tree looks like this:

So basically, we’re using a dumb parser hack to expand wildcards by cloning nodes.

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement