Postgres Pro
Downloads
Home   >   mailing lists

Thread: [SQL] Confusion about CREATE OPERATOR syntax.

[SQL] Confusion about CREATE OPERATOR syntax.

From
Raphael Araújo e Silva
Date:
Hi guys!

I'm the creator of the PostgreSQL Database Modeler and I'm improving the reverse engineering feature after some requests from the users.

I've stumbled on a bug when importing operators and while I was writing a fix for that bug a great confusion came into my mind. It's about the CREATE OPERATOR syntax, more specifically, about the LEFTARG and RIGHTARG attributes. 

I've read the docs but cannot find a proper answer and that's why I'm here... I hope you guys can help me. Well, my doubt is: when specifying LEFTARG and RIGHTARG the datatypes should be written in the complete form? By "complete form" I mean specifying the precision and/or length... For instance, I want to create an operator to handle certain operation between two numeric(5,2) values, said that, my operator should be written as:

CREATE OPERATOR ++ (
 PROCEDURE = func_name(numeric,numeric),
 LEFTARG = numeric,
 RIGHTARG = numeric
)

Or(?):

CREATE OPERATOR ++ (
 PROCEDURE = func_name(numeric,numeric),
 LEFTARG = numeric(5,2),
 RIGHTARG = numeric(5,2)
)

Well, I've tested both forms and them work fine. If I try to run one after another an error will raise for the second reporting that the operator already exists. Is PostgreSQL simply interpreting them as the same? Is the precision/length ignored/not needed?

Thanks in advance and forgive my rusty English, I'm Brazilian! :)
--
Raphael Araújo e Silva
pgModeler Project Developer


Re: [SQL] Confusion about CREATE OPERATOR syntax.

From
"David G. Johnston"
Date:
On Tuesday, September 5, 2017, Raphael Araújo e Silva <raphael@pgmodeler.com.br> wrote:
I've read the docs but cannot find a proper answer and that's why I'm here... I hope you guys can help me. Well, my doubt is: when specifying LEFTARG and RIGHTARG the datatypes should be written in the complete form? By "complete form" I mean specifying the precision and/or length... For instance, I want to create an operator to handle certain operation between two numeric(5,2) values, said that, my operator should be written as:

... 
Well, I've tested both forms and them work fine. If I try to run one after another an error will raise for the second reporting that the operator already exists. Is PostgreSQL simply interpreting them as the same? Is the precision/length ignored/not needed?

My understanding and your experience match, the "typmod" aspect of the base data type is ignored.  Here specifically and also  generally when dealing with functions.  So, no, you cannot write two functions/operators whose inputs distinguish/differ in the type modifier - and are thus the same underlying type.

Reading the docs for numeric it seems that the application of the modifier is only used during input value parsing - though table columns retain the info so that column literal inputs can be correctly processed.  When dealing with functions the inputs to the functions are already existing and typed values and so no input value parsing is needed.

David J.