join functions

From: Zotov <zotov(at)oe-it(dot)ru>
To: pgsql-hackers(at)postgresql(dot)org
Subject: join functions
Date: 2011-01-05 00:12:26
Message-ID: 4D23B76A.5090402@oe-it.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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(at)oe-it(dot)ru

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Josh Berkus 2011-01-05 00:45:54 Re: WIP: Range Types
Previous Message David Fetter 2011-01-04 23:36:56 Re: ALTER EXTENSION UPGRADE patch v1