Re: Overloading functions that are used by operators.

Dave Page wrote:

>  
>
>     -----Original Message-----
>     *From:* Donald Fraser [mailto:demolish@cwgsy.net]
>     *Sent:* 09 July 2003 11:04
>     *To:* [pgADMIN]
>     *Subject:* Re: [pgadmin-support] Overloading functions that are
>     used by operators.
>
>     Now back to pgAdmin III: now that I have created this new
>     overloaded function, pgAdmin III will fail when it trys to
>     populate the "Operators" section of the public schema. Why -
>     because I now have two functions named 'textcat', which is
>     perfectly legal but pgAdmin is making an assumption. The
>     assumption is that the name of the function associated to an
>     operator defined by pg_operator.oprcode is unique. Operators don't
>     just use the name of the function to decide which function to call
>     - they also have all of the information about the arguments. That
>     is how an operator knows exactly which function to call. Hence
>     pg_operator.oprcode is not the sole means for deciding which
>     function will be called, which is what pgAdmin III is assuming. 
>      
>
> An operator function is selected by it's name, and the left and/or 
> right operands. pgAdmin is doing this because the query uses 
> pg_operator.oprleft and oprright so it knows the types.
>
>     I also noticed that in pg_catalog using pgAdmin II there are 643
>     operators yet pgAdmin III reports only 596?
>      
>
> pgAdmin II is correct - there are 643 operators in pg_catalog in 
> PostgreSQL 7.3.x. The major reason why pgAdmin II gets it right is 
> because unlike pgAdmin III it simply does a select on pg_operator. The 
> rest of the details (function/type names etc) are retrieved from 
> internal caches.
>  
> In pgAdmin III's case, the much more complex query obviously has a 
> problem somewhere as it is not returning the correct number of operators.
>  
> I think the correct query is:
>  
> SELECT op.oid, op.oprname, pg_get_userbyid(op.oprowner) as opowner, 
> op.oprkind, op.oprcanhash,
>                op.oprleft, op.oprright, lt.typname as lefttype, 
> rt.typname as righttype, et.typname as resulttype,
>                co.oprname as compop, ne.oprname as negop, lso.oprname 
> as leftsortop, rso.oprname as rightsortop,
>                lco.oprname as lscmpop, gco.oprname as gtcmpop,
>                po.proname as operproc, pj.proname as joinproc, 
> pr.proname as restrproc, description
>           FROM pg_operator op
>           LEFT OUTER JOIN pg_type lt ON lt.oid=op.oprleft
>           LEFT OUTER JOIN pg_type rt ON rt.oid=op.oprright
>           JOIN pg_type et on et.oid=op.oprresult
>           LEFT OUTER JOIN pg_operator co ON co.oid=op.oprcom
>           LEFT OUTER JOIN pg_operator ne ON ne.oid=op.oprnegate
>           LEFT OUTER JOIN pg_operator lso ON lso.oid=op.oprlsortop
>           LEFT OUTER JOIN pg_operator rso ON rso.oid=op.oprrsortop
>           LEFT OUTER JOIN pg_operator lco ON lco.oid=op.oprltcmpop
>           LEFT OUTER JOIN pg_operator gco ON gco.oid=op.oprgtcmpop
>           JOIN pg_proc po ON po.oid=op.oprcode
>           LEFT OUTER JOIN pg_proc pr ON pr.oid=op.oprrest
>           LEFT OUTER JOIN pg_proc pj ON pj.oid=op.oprjoin
>           LEFT OUTER JOIN pg_description des ON des.objoid=op.oid
>  
> Note the addition of LEFT OUTER to the lt and rt table joins. It looks 
> like the unary operators were getting ignored.
>  
> Andreas: Please check this and confirm I'm right.

Ah no, I just thought that binary operators are only half operators, and 
we only want to display complete ones, right? ;-)

Regards,
Andreas