PostgreSQL Discussion - filtering after join

Author

filtering after join

andrew

2006-01-26, 4:56 pm

I want to use a UDF to filter tuples t that are generated after a join.
More specifially, I have a UDF foo(record), which computes a value for
a given tuple. I can do the filtering before the join. e.g.:

select * from A, B where foo(A)<2 and A.a=B.b;

But I want to apply foo() to the tuples generated by the join
operation. How can I do that?
Thanks!
--
andrew

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Alban Hertroys

2006-01-26, 4:57 pm

andrew wrote:
> I want to use a UDF to filter tuples t that are generated after a join.
> More specifially, I have a UDF foo(record), which computes a value for
> a given tuple. I can do the filtering before the join. e.g.:
>
> select * from A, B where foo(A)<2 and A.a=B.b;

I suppose you mean where foo(A.a) < 2?
I've never seen SP's being applied to entire tables, especially inside a
where clause.

Next to that, the planner can't do much in this case (according to a
thread here from this week), so you're likely to be stuck with
sequential scans.

> But I want to apply foo() to the tuples generated by the join
> operation. How can I do that?

You should be able to use something like (rewrote your join as well):
select foo(some_column) from A left join b on (A.a=B.b) where A.a < 2;

If that's not what you mean to do, could you be a bit clearer as to what
you're trying to achieve?

--
Alban Hertroys
alban@magproductions
.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

//Showing your Vision to the World//

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Sponsored Links