Postgres Pro
Downloads
Home   >   mailing lists

Thread: join functions

join functions

From
Zotov
Date:
Hello, Hackers!<br /> We have a project developed at Interbase and Firebird.<br /> Now we try use PostgreSQL and have
someproblem<br /><br /> Why doesn`t work this query?<br /> select table1.field1, func1.field2 from table1 left outer
joinfunc1(<font color="#ff0000">table1.field1</font>) on true where func1.field3 in (20, 100);<br /><br /> If i have
otherthan LEFT OUTER JOIN I can understand why <br />   ERROR:  invalid reference to FROM-clause entry for table
"table1"<br/><br /> but why here? <br />   for each row of table1 just evaluate func1(table1.field1)<br /><br /> To
reproduceexec this script:<br /><br /> drop table if exists table1;<br /> create table table1(field1 integer);<br />
createor replace function func1(inputparam integer) <br />   returns table(field1 integer, field2 integer, field3
integer)as <br /> $BODY$<br /> begin <br />   field1 := inputparam * 2;<br />   field2 := inputparam * 3;<br />  
field3:= inputparam * 4;<br />   return next;<br /><br />   inputparam := inputparam * inputparam;<br />   field1 :=
inputparam* 2;<br />   field2 := inputparam * 3;<br />   field3 := inputparam * 4;<br />   return next;<br /> end;<br
/>$BODY$<br /> LANGUAGE plpgsql VOLATILE;<br /><br /> insert into table1 values(5);<br /><br /> --select table1.field1,
func1.field2from table1 left outer join func1(table1.field1) on true where func1.field3 in (20, 100);<br /> select
table1.field1,func1.field2 from table1 left outer join func1(5) on true where func1.field3 in (20, 100);<br /><br /><br
/>Please help resolve this problem!<br /><br /><pre class="moz-signature" cols="72">-- 
 
С уважением,
Зотов Роман Владимирович
руководитель Отдела инструментария 
ЗАО "НПО Консультант"
г.Иваново, ул. Палехская, д. 10
тел./факс: (4932) 41-01-21
mailto: <a class="moz-txt-link-abbreviated" href="mailto:zotov@oe-it.ru">zotov@oe-it.ru</a></pre>

Re: join functions

From
Nicolas Barbier
Date:
2011/1/5 Zotov <zotov@oe-it.ru>:

> 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);
>
> If i have other than LEFT OUTER JOIN I can understand why
>   ERROR:  invalid reference to FROM-clause entry for table "table1"
>
> but why here?
>   for each row of table1 just evaluate func1(table1.field1)

That seems like a use case for LATERAL, which is not supported yet.

Some recent discussion seems to be
<URL:http://archives.postgresql.org/pgsql-hackers/2009-09/msg00292.php>.

Nicolas

Re: join functions

From
Zotov
Date:
07.01.2011 13:01, Nicolas Barbier пишет:
> That seems like a use case for LATERAL, which is not supported yet.
>
> Some recent discussion seems to be
> <URL:http://archives.postgresql.org/pgsql-hackers/2009-09/msg00292.php>.
>
> Nicolas

Thank you for your answer. Sorry, what i don`t find it myself.
But I can`t see when it will be done? If nobody can do this (maybe no 
time) what i can do to help?! I know C - Language only to read code. 
(I`m Delphi-programmer) and this is not that feature what i can do 
myself. I can try, but... It`s look like difficult.


-- 
С уважением,
Зотов Роман Владимирович
руководитель Отдела инструментария
ЗАО "НПО Консультант"
г.Иваново, ул. Палехская, д. 10
тел./факс: (4932) 41-01-21
mailto: zotov@oe-it.ru

Re: join functions

From
Robert Haas
Date:
On Fri, Jan 7, 2011 at 8:34 AM, Zotov <zotov@oe-it.ru> wrote:
> Thank you for your answer. Sorry, what i don`t find it myself.
> But I can`t see when it will be done? If nobody can do this (maybe no time)
> what i can do to help?! I know C - Language only to read code. (I`m
> Delphi-programmer) and this is not that feature what i can do myself. I can
> try, but... It`s look like difficult.

Yeah, it's difficult.  I don't think it can be done without the
generalized inner-indexscan stuff Tom was working on a few months
back, but I'm not sure what the status of that is at the moment.

For now, your best option is probably to write a PL/pgsql function
that iterates over table1 and then does a SELECT that calls func1()
and does whatever else for each row in table1.  This can be a little
slow but I think it's the only option in existing releases of
PostgreSQL.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: join functions

From
Marko Tiikkaja
Date:
On 5 Jan 2011, at 02:12, Zotov <zotov@oe-it.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