Re: plan for function returning table combined with condition

Thierry Henrio <thierry(dot)henrio(at)gmail(dot)com> writes:
> I made a function out of this sql:

> create or replace function expand_shop_opening_times() returns table(id
> int, name text, day int, startt time, endt time)
> as $$
> select id, name, day::int, (value -> 0 ->> 0)::time as startt, (value -> 0
> ->> 1)::time as endt from (
> select s.id, s.name, j.* from shops s cross join
> jsonb_each(s.opening_times) as j(day, value)
> ) t
> $$ language sql

> So I can use it like so (A):

> select id, name from expand_shop_opening_times() where id=1307;

> The plan for statement (A) is:

> Function Scan on expand_shop_opening_times (cost=0.25..12.75 rows=5
> width=36) (actual time=15.950..16.418 rows=7 loops=1)
> Filter: (id = 1307)
> Rows Removed by Filter: 10540
> Planning Time: 0.082 ms
> Execution Time: 16.584 ms

You want this SQL function to be inlined, but it isn't being.
I think the reason is that (by default) it's VOLATILE, and
inline_set_returning_function doesn't like that:

* Forget it if the function is not SQL-language or has other showstopper
* properties. In particular it mustn't be declared STRICT, since we
* couldn't enforce that. It also mustn't be VOLATILE, because that is
* supposed to cause it to be executed with its own snapshot, rather than
* sharing the snapshot of the calling query. We also disallow returning
* SETOF VOID, because inlining would result in exposing the actual result
* of the function's last SELECT, which should not happen in that case.

So try adding STABLE to the function definition.

(This could be better documented, perhaps.)

regards, tom lane