Re: immutable functions vs. join for lookups ?

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> It would be interesting sometime to try to teach the planner about
> inlining SQL-language functions to become joins. That is, given
>
> create function id2name(int) returns text as
> 'select name from mytab where id = $1' language sql stable;
>
> select uid, id2name(uid) from othertab where something;
>
> I think that in principle this could automatically be converted to
>
> select uid, name from othertab left join mytab on (uid = id) where something;

The Inlining of the function is presumably a side-issue. I have tons of
queries that use subqueries in the select list for which the same behaviour
would be appropriate.

Things like

select uid, (select name from mytab where id = uid) as name from othertab ...

> There are some pitfalls though, particularly that you'd have to be able to
> prove that the function's query couldn't return more than one row (else the
> join might produce more result rows than the original query).

Or just have a special join type that has the desired behaviour in that case.
Ie, pretend the query was really

SELECT * FROM othertab LEFT SINGLE JOIN mytab ...

Where "LEFT SINGLE JOIN" is an imaginary syntax that doesn't actually have to
exist in the parser, but exists in the planner/executor and behaves
differently in the case of duplicate matches.

Actually I could see such a syntax being useful directly too.

--
greg