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

[Donald Fraser]

I have a question regarding operators which are causing problems with the new pgAdmin III software being developed and my database. The problem has been discussed in depth with the pgAdmin III development team and they have not been able to resolve the issue and suggested that I have either done something that I shouldn't have of or that there is a possible bug with postgresql.   Background information: I have my own data type ('citext') that uses many of the built-in system functions based on the fact that the storage is identical to the data type 'text'. For example I have overloaded the function 'textcat' and the definition I have used is: CREATE FUNCTION pg_catalog.textcat(citext, citext) RETURNS citext AS 'textcat' LANGUAGE 'internal' IMMUTABLE STRICT; When you view the functions via the pg_proc table you will see that there are two functions with 'textcat' in the column pg_proc.proname. I have been using the database for over a year and had no reported problems with using the operator || on data types 'text', 'varchar', 'bpchar' or calling the overloaded function 'textcat(citext, citext)'.   First question: Am I allowed to overload functions, for example the 'textcat' function, when they are being used by system operators?   If the answer is no then read no further - the problem is mine and I will have to work around it.   The system has at least three operators such as: ||(text,text) ||(varchar, text) ||(bpchar, text) All use the function 'textcat'.   Now the new pgAdmin III software interrogates the pg_operator table to extract as much information as possible about the operators. They are using the following SQL statement to retrieve the information that they want.   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 JOIN pg_type lt ON lt.oid=op.oprleft 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 WHERE op.oprnamespace = 2200::oid ORDER BY op.oprname   The offending part of this SQL statement is the join: JOIN pg_proc po ON po.oid=op.oprcode   It produces the error message "ERROR: There is more than one procedure named textcat". Investigation into this has shown that the type conversion of the column named pg_operator.oprcode to type oid is done by the function call to: regprocin('textcat') My question would be - what is the correct way to find the function's oid that this operator uses? But first read on in case it is not necessary... It seems to me that the join statements they are using are unnecessary so long as the column pg_operator.oprcode has the identical name to its matching function that has its name defined by column pg_proc.proname, which seems to be the only data that they are using.   On a similar basis, do the columns named pg_operator.oprjoin and pg_operator.oprrest have identical names defined by the column pg_proc.proname for which they are also retrieving? If so then those corresponding joins are also unnecessarily.   Many thanks in advance, regards Donald Fraser.