Обсуждение: RE: [HACKERS] Associative Operators? (Was: Re: [NOVICE] Out of f rying pan, into fire)
RE: [HACKERS] Associative Operators? (Was: Re: [NOVICE] Out of f rying pan, into fire)
От
Michael Davis
Дата:
However it is implemented, I would really enjoy this enhancement. Additionally, it would be nice if I could create a new operator using C and have this new operator be associative if desired. Speaking of this, If either LASTNAME or FIRSTNAME is NULL then the result of ((LASTNAME || ',' ) || FIRSTNAME) will return NULL. I would like to be able to alter this such that the result will contain what ever is not NULL. I tried to create a C function to overcome this but noticed that if any parameter in my C function is NULL then the C function always returns NULL. I saw some references in the archives about this issue but was unable to determine where it was left. What is the status of this issue? Thanks, Michael -----Original Message-----From: Bruce Momjian [SMTP:maillist@candle.pha.pa.us]Sent: Tuesday, March 16, 1999 3:24 PMTo: clark.evans@manhattanproject.comCc: hackers@postgreSQL.orgSubject: Re: [HACKERS] Associative Operators? (Was:Re: [NOVICE] Out of frying pan, into fire) > Seth McQuale pointed out that the follwing does not work:> SELECT LASTNAME || ',' || FIRSTNAME [AS] NAME FROM FRIENDS;>> The solution, was:> SELECT ( LASTNAME || ',' ) || FIRSTNAME AS NAME FROM FRIENDS;> > I looked at pg_operatorand didn't see any flag to mark> an operator as 'associative'. Perhaps if we added a flag> like this, the re-writesystem could be modified to handle> cases like this.> > Thoughts?> > Clark Evans> > My guess is that we should auto-left-associate functions like || if noparens are present. It would be a small change to the parser. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,Pennsylvania 19026
Michael Davis wrote: > Speaking of this, If either LASTNAME or FIRSTNAME is NULL then the result of > ((LASTNAME || ',' ) || FIRSTNAME) will return NULL. I would like to be able > to alter this such that the result will contain what ever is not NULL. I > tried to create a C function to overcome this but noticed that if any > parameter in my C function is NULL then the C function always returns NULL. > I saw some references in the archives about this issue but was unable to > determine where it was left. What is the status of this issue? Although I feel initial opposition to this idea, on second consideration, I guess it is reasonable behavior, in Oracle, the NVL function and the DECODE function both handle NULL arguments without having the result be NULL. However, I'm unaware of any other exceptions in the Oracle database on this issue. I believe that user defined functions are not allowed to have special NULL treatment -- perhaps Oracle has DECODE and NVL hard coded deep in the guts of their query processor, while the other functions arn't. Would a compromise be to add DECODE and NVL ? Clark
Re: [HACKERS] Associative Operators? (Was: Re: [NOVICE] Out of frying pan, into fire)
От
"Ross J. Reedstrom"
Дата:
Clark Evans wrote: > <snipped discussion of 'something || NULL ' returning non-NULL> > However, I'm unaware of any other exceptions in the Oracle > database on this issue. I believe that user defined functions > are not allowed to have special NULL treatment -- perhaps > Oracle has DECODE and NVL hard coded deep in the guts of > their query processor, while the other functions arn't. > > Would a compromise be to add DECODE and NVL ? What do DECODE and NVL do? Ross -- Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
"Ross J. Reedstrom" wrote: > What do DECODE and NVL do? TABLE_A COLROW COLVALUE ----- -------- ROW1 a ROW2 b ROW3 <- NULL ROW4 d ROW5 <- NULL 5 rows -- NVL function: -- -- This function takes a value of any time, and checks -- to see if the value IS NULL. If argument is not null, -- then it simply returns it's argument. Otherwise, it -- returns what is provided as the second argument. -- SELECT COLROW, NVL(COLVALUE,'XX') AS NOT_NULL_COLVALUE FROM TABLE_A COLROW NOT_NULL_COLVALUE ----- -------- ROW1 a ROW2 b ROW3 XX ROW4 d ROW5 XX 5 rows val,lookup,val,default -- DECODE function ( CASE/SWITCH like function ) -- -- This function takes an even number of arguments, N -- -- The function compaires the first argument against each -- even numbered argument in the argumet list. If it is -- a match, then it returns the following value in the -- argument list. If there is no match, then the last -- argument (the default value) is returned. For matching -- purposes a NULL = NULL. The first argument and the -- middle even arguments must all be the same type, as well -- as the last argument and the middle odd arguments. -- SELECT COLROW, DECODE(COLVAL, 'd',4, 'e',0, NULL,9, 1 ) AS DECODE_COLVALUE FROMTABLE_A COLROW DECODE_COLVALUE ----- -------- ROW1 1 ROW2 1 ROW3 9 ROW4 4 ROW5 9 5 rows Hope this helps! Clark