Re: [HACKERS] join functions
On Fri, Jan 7, 2011 at 8:34 AM, Zotov zo...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] join functions
On 5 Jan 2011, at 02:12, Zotov zo...@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
[HACKERS] join functions
Hello, Hackers! We have a project developed at Interbase and Firebird. Now we try use PostgreSQL and have some problem 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) To reproduce exec this script: drop table if exists table1; create table table1(field1 integer); create or replace function func1(inputparam integer) returns table(field1 integer, field2 integer, field3 integer) as $BODY$ begin field1 := inputparam * 2; field2 := inputparam * 3; field3 := inputparam * 4; return next; inputparam := inputparam * inputparam; field1 := inputparam * 2; field2 := inputparam * 3; field3 := inputparam * 4; return next; end; $BODY$ LANGUAGE plpgsql VOLATILE; insert into table1 values(5); --select table1.field1, func1.field2 from table1 left outer join func1(table1.field1) on true where func1.field3 in (20, 100); select table1.field1, func1.field2 from table1 left outer join func1(5) on true where func1.field3 in (20, 100); Please help resolve this problem! -- С уважением, Зотов Роман Владимирович руководитель Отдела инструментария ЗАО НПО Консультант г.Иваново, ул. Палехская, д. 10 тел./факс: (4932) 41-01-21 mailto: zo...@oe-it.ru
Re: [HACKERS] join functions
2011/1/5 Zotov zo...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] join functions
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: zo...@oe-it.ru -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
