Thread: filtering after join
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
On Wed, Jan 25, 2006 at 06:27:33PM +0100, 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; > > But I want to apply foo() to the tuples generated by the join > operation. How can I do that? Is this what you're looking for? select * from (select * from A, B where A.a = B.b) as s where foo(s) < 2; -- Michael Fuhr
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; What makes you think that the filtering happens before the join here? And why would it matter? The results should be all the same. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Sorry for the confusion. This is what i meant. Thanks, Michael. select * from (select * from A, B where A.a = B.b) as s where foo(s) < 2; On 1/25/06, Peter Eisentraut <peter_e@gmx.net> wrote: > 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; > > What makes you think that the filtering happens before the join here? > And why would it matter? The results should be all the same. > > -- > Peter Eisentraut > http://developer.postgresql.org/~petere/ > -- andrew
andrew wrote: > Sorry for the confusion. This is what i meant. Thanks, Michael. > > select * > from (select * from A, B where A.a = B.b) as s > where foo(s) < 2; > > On 1/25/06, Peter Eisentraut <peter_e@gmx.net> wrote: > > 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; Note that these two queries will produce the same internal execution plan, so if you wanted to make some difference you will not. -- Peter Eisentraut http://developer.postgresql.org/~petere/
But the function foo() would produce different values for the two queries, so the result will be different. A simple example is foo() computes the sum of all the integer fields of the input record. On 1/26/06, Peter Eisentraut <peter_e@gmx.net> wrote: > andrew wrote: > > Sorry for the confusion. This is what i meant. Thanks, Michael. > > > > select * > > from (select * from A, B where A.a = B.b) as s > > where foo(s) < 2; > > > > On 1/25/06, Peter Eisentraut <peter_e@gmx.net> wrote: > > > 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; > > Note that these two queries will produce the same internal execution > plan, so if you wanted to make some difference you will not. > > -- > Peter Eisentraut > http://developer.postgresql.org/~petere/ > -- andrew
andrew wrote: > But the function foo() would produce different values for the two > queries, so the result will be different. > A simple example is foo() computes the sum of all the integer fields > of the input record. OK, I see now where you're getting at. You want to combine the record type of A and B into one. Then the proposed solution is right. -- Peter Eisentraut http://developer.postgresql.org/~petere/
How will the query planner do for a nesting query? Treat the subqueries as multiple queries and then link them together? where can I find the information (codes or documents)? Thanks. On 1/26/06, Peter Eisentraut <peter_e@gmx.net> wrote: > andrew wrote: > > But the function foo() would produce different values for the two > > queries, so the result will be different. > > A simple example is foo() computes the sum of all the integer fields > > of the input record. > > OK, I see now where you're getting at. You want to combine the record > type of A and B into one. Then the proposed solution is right. > > -- > Peter Eisentraut > http://developer.postgresql.org/~petere/ > -- andrew
andrew wrote: > How will the query planner do for a nesting query? Treat the > subqueries as multiple queries and then link them together? > where can I find the information (codes or documents)? Look at the execution plan using the EXPLAIN command. -- Peter Eisentraut http://developer.postgresql.org/~petere/
I can see the final plan by using the EXPLAIN command. But I want to know the procedure of the planner in handling nesting queries. Can you direct me to the corresponding part of the code and/or the documents? Thanks. On 1/26/06, Peter Eisentraut <peter_e@gmx.net> wrote: > andrew wrote: > > How will the query planner do for a nesting query? Treat the > > subqueries as multiple queries and then link them together? > > where can I find the information (codes or documents)? > > Look at the execution plan using the EXPLAIN command. > > -- > Peter Eisentraut > http://developer.postgresql.org/~petere/ > -- andrew
I got errors in this query. I have a function complete(record) which takes a generic record type data. But it seems cannot be applied to a sub-select result: backend> explain select * from (select * from Person,Auction where Person.id=Auction.seller) as s where complete(s) QUERY: explain select * from (select * from Person,Auction where Person.id=Auction.seller) as s where complete(s) ERROR: Cannot pass result of sub-select or join s to a function On 1/27/06, andrew <andrew.ylzhou@gmail.com> wrote: > I can see the final plan by using the EXPLAIN command. But I want to > know the procedure of the planner in handling nesting queries. Can you > direct me to the corresponding part of the code and/or the documents? > Thanks. > > On 1/26/06, Peter Eisentraut <peter_e@gmx.net> wrote: > > andrew wrote: > > > How will the query planner do for a nesting query? Treat the > > > subqueries as multiple queries and then link them together? > > > where can I find the information (codes or documents)? > > > > Look at the execution plan using the EXPLAIN command. > > > > -- > > Peter Eisentraut > > http://developer.postgresql.org/~petere/ > > > > > -- > andrew > -- andrew
andrew <andrew.ylzhou@gmail.com> writes:
> I got errors in this query. I have a function complete(record) which
> takes a generic record type data. But it seems cannot be applied to a
> sub-select result:
As I seem to recall having mentioned several times already, PG 7.3 is
really, really weak in this area. (If you ever got it to work, which
I doubt you will, you'd probably then start to notice how badly it
leaks memory for whole-row operations...)
You should either forget this idea or invest the effort to move up
to PG 8.1.
regards, tom lane
Sorry. I am working on a research prototype built on 7.3 which contains a cosiderable amount of efforts. The original developers of the prototype have no interest in upgrading it. It is also impossible for me to upgrade it by myself. Could you give me any tips to sovle the current problem? Thanks! On 1/27/06, Tom Lane <tgl@sss.pgh.pa.us> wrote: > andrew <andrew.ylzhou@gmail.com> writes: > > I got errors in this query. I have a function complete(record) which > > takes a generic record type data. But it seems cannot be applied to a > > sub-select result: > > As I seem to recall having mentioned several times already, PG 7.3 is > really, really weak in this area. (If you ever got it to work, which > I doubt you will, you'd probably then start to notice how badly it > leaks memory for whole-row operations...) > > You should either forget this idea or invest the effort to move up > to PG 8.1. > > regards, tom lane > -- andrew
I have traced the code. It exits when the argument is the result of a join or a subselect in function ParseFuncOrColumn(). The reason mentioned in the comments is lack of named tuple type. How can force it to create such a tuple type? is there a way? thanks a million times! On 1/27/06, Tom Lane <tgl@sss.pgh.pa.us> wrote: > andrew <andrew.ylzhou@gmail.com> writes: > > I got errors in this query. I have a function complete(record) which > > takes a generic record type data. But it seems cannot be applied to a > > sub-select result: > > As I seem to recall having mentioned several times already, PG 7.3 is > really, really weak in this area. (If you ever got it to work, which > I doubt you will, you'd probably then start to notice how badly it > leaks memory for whole-row operations...) > > You should either forget this idea or invest the effort to move up > to PG 8.1. > > regards, tom lane > -- andrew