Re: join functions

On 5 Jan 2011, at 02:12, Zotov <zotov(at)oe-it(dot)ru> wrote:
> Why doesn`t work this query?
> select table1.field1, func1.field2 from table1 left outer join func1
> (table1.field1) on true where func1.field3 in (20, 100);

The approach people usually use is:

SELECT
f1, (fn).field2
FROM
(
SELECT
field1 as f1, func1(field1) as fn
FROM
table1
OFFSET 0
) ss
WHERE
(fn).field3 IN (20, 100)
;

OFFSET 0 is there to prevent the function from getting called more
than once. Also note that this will scan the whole table. There
might be a way to avoid that by creating an index on ((func1
(field1)).field3) and removing OFFSET 0, but only if the function is
IMMUTABLE.

Regards,
Marko Tiikkaja