Tom Lane <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
