Thread: SQL function inlining

I'm having trouble figuring out when (if) the planner inlines sql
functions (I'm running 7.4).  I was assuming that pure sql functions
are kind of like views with parameters, but I can't seem to see any
cases where functions that select from a table get inlined.  For
instance:

create function login_count (integer) returns bigint
   language sql stable as
   'select count(*) from logins where user_id = $1;';

# explain select user_id, login_count(user_id) from users;
                           QUERY PLAN
--------------------------------------------------------------
Seq Scan on users  (cost=0.00..15418.36 rows=480189 width=4)

I'd hoped this would turn into a join on the logins and users tables,
but no joy.  Are there any circumstances (in 7.4) where such
functions get inlined?

Thanks.

- John Burger
   MITRE

"John D. Burger" <john@mitre.org> writes:
> I'm having trouble figuring out when (if) the planner inlines sql
> functions (I'm running 7.4).  I was assuming that pure sql functions
> are kind of like views with parameters, but I can't seem to see any
> cases where functions that select from a table get inlined.

That's 'cause there aren't any ... especially not in 7.4 ...
we inline simple scalar functions of the form "select scalar-expression"
but that's about it.

            regards, tom lane