Обсуждение: join functions
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>
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
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
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
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