Re: Overloading functions that are used by operators.

Message
----- Original Message -----
From: Dave Page
To: Donald Fraser
Cc: [ADMIN] ; [pgADMIN]
Sent: Tuesday, July 08, 2003 9:49 PM
Subject: RE: [pgadmin-support] Overloading functions that are used by operators.

Ahh, thanks. Was just looking at that.

Well I always thought you could overload functions in this way, however with your example below, although it creates it OK, PostgreSQL (not pgAdmin) gives the error 'ERROR: There is more than one function named textcat'.

As I said, I'm pretty sure that you can overload functions (in fact I know you can - there's one of mine in PostgreSQL 7.3!). I wonder if in this case the system knows that it can implicitly cast the arguments of one function to the other and hence the error is given?

Would this be the case for your citext datatype?

Regards, Dave.

Hi Dave and Andreas,
sorry about all the confusion I've created.
Hopefully this email will clear everything up as there are only two things at issue here - operators and function overloading, leave data types out even though they are partially involved, my custom type has nothing to do with it.
I have removed my data type all together and can still produce the problem by simply overloading any function that is also used by an operator. Hence the 'textcat' example I gave previously.

Here is another example that doesn't use any custom data types. Lets say I wanted to concatenate binary data together and so I define my own function in a 'C' module named 'bincat.so'. The function definition in postgres might be:
CREATE FUNCTION pg_catalog.textcat(bytea, bytea) RETURNS bytea AS 'bincat', 'bincat.so' LANGUAGE 'c' IMMUTABLE STRICT;
Now forget why you would give it a name such as 'textcat', the thing it that it is possible simply because postgresql allows and supports function overloading.

Everything in postgres will still work, even the operators for the data types 'text', 'varchar' and 'bpchar'. ('textcat' is used as operators for ||)
Function overloading is definitely allowed because you can look in the pg_catalog.pg_proc table for yourselves and see many internal functions such as 'abs' which are overloaded many times.

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.

The offending SQL in pgAdmin III are the joins that use the pg_operator.oprcode, pg_operator.oprrest and pg_operator.oprjoin columns.
For example:
JOIN pg_proc po ON po.oid=op.oprcode
postgresql has to convert the op.oprcode to an oid via a function call to "regprocin(op.oprcode)".
The function "regprocin" can only do this if the function name passed to it is unique within the table column pg_catalog.proname, otherwise it doesn't know which function oid it should return.
For example if you do: SELECT regprocin('abs') you get the same sort of error message that we are experiencing: "ERROR: There is more than one procedure named abs"

I had another idea that I thought would explain things much easier for you, but unfortunately it exposes yet another bug.
Try this:
CREATE OPERATOR !@ (PROCEDURE = abs, LEFTARG = int4);
I think you can see where I was going with this example. 'abs' is a function that already exists and is already overloaded many times by the system, no additional data types necessary or any additional function overloading. Unfortunately I don't get the error I was expecting pgAdmin III to display, but that's probably because pgAdmin III is not seeing this operator anywhere - not in 'public' or 'pg_catalog' schemas.
I also noticed that in pg_catalog using pgAdmin II there are 643 operators yet pgAdmin III reports only 596?

I hope that clears things up for you and that I haven't gone down the wrong road yet again.

Regards
Donald Fraser.