Обсуждение: Help with casting and comparing.
Hi, I need help finding out how to determine if two types are equality compatible and compare them. I am using the following call to check for equality between two values: DatumGetBool(\ FunctionCall2(&(fctx->tupleSetAttEQFunctions[attID]->eq_opr_finfo)\ , lvalue, rvalue)) The structure fctx->tupleSetAttEQFunctions[attID]->eq_opr_finfo currently holds a reference to an equality function that is proper for the type of lvalue and rvalue. Currently i only allow two values only of the same type but i wish to allow to compare values like "20.2"=?20.2 or 20=?20.0 etc... The first step is to find out if two attributes are equality and casting compatible, i.e., if one type can be cast to the other type so they can be compared. Or, just equality compatible and the casting is done somehow behind the scene. Finally, i have to use a function to compare the two values. -- Regards, Tzahi. -- Tzahi Fadida Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info WARNING TO SPAMMERS: see at http://members.lycos.co.uk/my2nis/spamwarning.html
On Wed, Jun 28, 2006 at 03:25:57PM +0300, Tzahi Fadida wrote: > Hi, > > I need help finding out how to determine if two types are equality compatible > and compare them. <snip> > Currently i only allow two values only of the same type but i wish to allow > to compare values like "20.2"=?20.2 or 20=?20.0 etc... > > The first step is to find out if two attributes are equality and casting > compatible, i.e., if one type can be cast to the other type so they can be > compared. Or, just equality compatible and the casting is done somehow behind > the scene. There's two ways an equality could work. For example, there are equality operators that take parameters of different types. That's the easy case. Then you have binary compatable types, and then types with actual conversion functions. Fortunatly the backend contains functions that do all this already. Check out parser/parse_oper.c, in particular oper() and compatable_oper(). You may have to be prepared to handle a parsetree to do the actual work. /* oper() -- search for a binary operator* Given operator name, types of arg1 and arg2, return oper struct.** IMPORTANT:the returned operator (if any) is only promised to be* coercion-compatible with the input datatypes. Do not usethis if* you need an exact- or binary-compatible match; see compatible_oper. ...etc... Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Martijn van Oosterhout <kleptog@svana.org> writes: > On Wed, Jun 28, 2006 at 03:25:57PM +0300, Tzahi Fadida wrote: >> I need help finding out how to determine if two types are equality compatible >> and compare them. > Fortunatly the backend contains functions that do all this already. > Check out parser/parse_oper.c, in particular oper() and > compatable_oper(). Note that this still leaves the question of what operator to search for, and where to look for it. The current system doesn't really provide an adequate way of identifying a suitable equality operator; you kind of have to take it on faith that people won't have made "=" do unexpected things (an assumption already violated by some builtin datatypes ...). We've been moving gradually in the direction of relying on btree operator classes to give us a better understanding of which operators really act like equality, but it's far from all done. The most recent thread about fixing this was http://archives.postgresql.org/pgsql-hackers/2006-02/msg00960.php Nothing much has been done since then as far as fixing foreign-key checks, but you might want to look at the code for interpreting row value comparisons (make_row_comparison_op in parse_expr.c). SelectSortFunction in tuplesort.c is another example of looking for btree info to infer the behavior of an operator. regards, tom lane
I looked around in the code and the whole thing looks complex and prone to breaking my code often, i.e., whenever someone will decide to change the casting/operators. I thought about just issuing in SPI_prepare query the proper casting like: SELECT a0::text,a1::text ... Casting to equal types (when neccessary) will allow me to just use regular equality functions. And perhaps the added benefit is that the casted values are cached? since i restart cursor scans often(by moving to start not reopening). The downside is that i noticed that the CTID is removed from the tuple if a cast occurs. Is there a way to tell postgresql to not remove the CTID? The other way, of course is to add CTID as an attribute in the query but it seems less efficient since i am accessing it repeatedly. On Wednesday 28 June 2006 18:12, Tom Lane wrote: > Martijn van Oosterhout <kleptog@svana.org> writes: > > On Wed, Jun 28, 2006 at 03:25:57PM +0300, Tzahi Fadida wrote: > >> I need help finding out how to determine if two types are equality > >> compatible and compare them. > > > > Fortunatly the backend contains functions that do all this already. > > Check out parser/parse_oper.c, in particular oper() and > > compatable_oper(). > > Note that this still leaves the question of what operator to search for, > and where to look for it. The current system doesn't really provide an > adequate way of identifying a suitable equality operator; you kind of > have to take it on faith that people won't have made "=" do unexpected > things (an assumption already violated by some builtin datatypes ...). > We've been moving gradually in the direction of relying on btree > operator classes to give us a better understanding of which operators > really act like equality, but it's far from all done. > > The most recent thread about fixing this was > http://archives.postgresql.org/pgsql-hackers/2006-02/msg00960.php > Nothing much has been done since then as far as fixing foreign-key > checks, but you might want to look at the code for interpreting row > value comparisons (make_row_comparison_op in parse_expr.c). > SelectSortFunction in tuplesort.c is another example of looking for > btree info to infer the behavior of an operator. > > regards, tom lane -- Regards, ��������Tzahi. -- Tzahi Fadida Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info WARNING TO SPAMMERS: �see at http://members.lycos.co.uk/my2nis/spamwarning.html
On Thu, Jul 06, 2006 at 07:43:20PM +0300, Tzahi Fadida wrote: > The downside is that i noticed that the CTID is removed from the tuple > if a cast occurs. Is there a way to tell postgresql to not remove the > CTID? Err, the fact the ctid is removed is really just a side-effect. With no adjusting of the output, you may just get the actual on-disk tuple. But as soon as you do some manipulation, you get a new tuple. > The other way, of course is to add CTID as an attribute in the query > but it seems less efficient since i am accessing it repeatedly. If you want the ctid, you have to ask for it. But this seems a little like premature optimisation. First, make it work, then make it fast. Once you've got it working you can worry about performance. Adding an extra column to the output costs very, very little compared to other things... -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
On Thursday 06 July 2006 21:55, Martijn van Oosterhout wrote: > On Thu, Jul 06, 2006 at 07:43:20PM +0300, Tzahi Fadida wrote: > > The downside is that i noticed that the CTID is removed from the tuple > > if a cast occurs. Is there a way to tell postgresql to not remove the > > CTID? > > Err, the fact the ctid is removed is really just a side-effect. With no > adjusting of the output, you may just get the actual on-disk tuple. But > as soon as you do some manipulation, you get a new tuple. > > > The other way, of course is to add CTID as an attribute in the query > > but it seems less efficient since i am accessing it repeatedly. > > If you want the ctid, you have to ask for it. > > But this seems a little like premature optimisation. First, make it > work, then make it fast. Once you've got it working you can worry about > performance. Adding an extra column to the output costs very, very > little compared to other things... It works, i use this technique for index accesses. I am not worried about getting this to work since i already tried this. I am more worried about optimization. Well, it is probably the lesser evil of dealing with casting. P.s. the code is running and can be found here: http://pgfoundry.org/projects/fulldisjunction/ -- Regards, ��������Tzahi. -- Tzahi Fadida Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info WARNING TO SPAMMERS: �see at http://members.lycos.co.uk/my2nis/spamwarning.html