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: zotov@oe-it.ru
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