Re: Overloading functions that are used by operators.

Dave Page wrote:

>
>
> -----Original Message-----
> *From:* Donald Fraser [mailto:demolish(at)cwgsy(dot)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