Thread: RE: [HACKERS] Associative Operators? (Was: Re: [NOVICE] Out of f rying pan, into fire)

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

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