Re: Overloading functions that are used by operators.
От | Andreas Pflug |
---|---|
Тема | Re: Overloading functions that are used by operators. |
Дата | |
Msg-id | 3F0B3210.1080508@pse-consulting.de обсуждение исходный текст |
Ответ на | Overloading functions that are used by operators. ("Donald Fraser" <demolish@cwgsy.net>) |
Список | pgadmin-support |
Donald Fraser wrote: > 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. > Donald, we discussed that this should go to pgsql-bugs, not any admin list. While there might be some question if joining pg_proc to pg_type is necessary for pgAdmin3, but it's certainly legal to query which pg_proc is used for a type. The underlying question for your case is whether it's allowed to create overloaded functions of a function that's used for a type. regprocin will not like this, so CREATE TYPE should deny creation if input or output function isn't unique by name. Regards, Andreas
В списке pgadmin-support по дате отправления: