Re: [pgadmin-support] Overloading functions that are used by operators.

[Dave Page]

Title: Message

-----Original Message----- From: Donald Fraser [mailto:[EMAIL PROTECTED] 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.   Regards, Dave.