Tom Lane <tgl@sss.pgh.pa.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