Overloading functions that are used by operators. - Mailing list pgadmin-support
| From | Donald Fraser |
|---|---|
| Subject | Overloading functions that are used by operators. |
| Date | |
| Msg-id | 00c801c3458b$8c139490$1664a8c0@DEMOLITION Whole thread Raw |
| Responses |
Re: Overloading functions that are used by operators.
|
| List | pgadmin-support |
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.
pgadmin-support by date: