Re: Named Operators
> On 8 Feb 2023, at 16:57, Tom Lane wrote: > I do not think this proposal is going anywhere as-written. Reading this thread, it seems there is concensus against this proposal in its current form, and no updated patch has been presented, so I will mark this as Returned with Feedback. Please feel free to resubmit to a future CF when there is renewed interest in working on this. -- Daniel Gustafsson
Re: Named Operators
I wrote:
> This approach does have a couple of shortcomings:
> * You still have to invent an operator name, even if you never
> plan to use it in queries. This is just cosmetic though.
> It's not going to matter if the operator name is long or looks like
> line noise, if you only need to use it a few times in setup DDL.
Oh, one other thought is that we could address that complaint
by allowing OPERATOR(identifier), so that your DDL could use
a meaningful name for the operator. I see that we don't
actually support OPERATOR() right now in CREATE OPERATOR or
ALTER OPERATOR:
regression=# create operator operator(+) (function = foo);
ERROR: syntax error at or near "("
LINE 1: create operator operator(+) (function = foo);
^
but I doubt that'd be hard to fix.
regards, tom lane
Re: Named Operators
Peter Eisentraut writes:
> On 12.01.23 14:55, Matthias van de Meent wrote:
>>> Matter of taste, I guess. But more importantly, defining an operator
>>> gives you many additional features that the planner can use to
>>> optimize your query differently, which it can't do with functions. See
>>> the COMMUTATOR, HASHES, etc. clause in the CREATE OPERATOR command.
>> I see. Wouldn't it be better then to instead make it possible for the
>> planner to detect the use of the functions used in operators and treat
>> them as aliases of the operator? Or am I missing something w.r.t.
>> differences between operator and function invocation?
>> E.g. indexes on `int8pl(my_bigint, 1)` does not match queries for
>> `my_bigint + 1` (and vice versa), while they should be able to support
>> that, as OPERATOR(pg_catalog.+(int8, int8)) 's function is int8pl.
> I have been thinking about something like this for a long time.
> Basically, we would merge pg_proc and pg_operator internally. Then, all
> the special treatment for operators would also be available to
> two-argument functions.
I had a thought about this ...
I do not think this proposal is going anywhere as-written.
There seems very little chance that we can invent a syntax that
is concise, non-ugly, and not likely to get blindsided by future
SQL spec extensions. Even if we were sure that, say, "{foo}"
was safe from spec interference, the syntax "a {foo} b" has
exactly nothing to recommend it compared to "foo(a,b)".
It's not shorter, it's not standard, it won't help any pre-existing
queries, and it can't use function-call features such as named
arguments.
As Matthias said, what we actually need is for the planner to be able
to optimize function calls on the same basis as operators. We should
tackle that directly rather than inventing new syntax.
We could go after that by inventing a bunch of new function properties
to parallel operator properties, but there is a simpler way: just
teach the planner to look to see if a function call is a call of the
underlying function of some operator, and if so treat it like that
operator. Right now that'd be an expensive lookup, but we could
remove that objection with an index on pg_operator.oprcode or a
single new field in pg_proc.
This approach does have a couple of shortcomings:
* You still have to invent an operator name, even if you never
plan to use it in queries. This is just cosmetic though.
It's not going to matter if the operator name is long or looks like
line noise, if you only need to use it a few times in setup DDL.
* We could not extend this to support index functions with more than
two arguments, a request we've heard once in awhile in the past.
Our answer to that so far has been "make a function/operator with
one indexed argument and one composite-type argument", which is a
bit of an ugly workaround but seems to be serviceable enough.
On the whole I don't think these shortcomings are big enough
to justify all the work that would be involved in attaching
operator-like optimization information directly to functions.
(To mention just one nontrivial stumbling block: do you really
want to invent "shell functions" similar to the shell-operator
hack? If not, how are you going to handle declaration of
commutator pairs?)
In the long run this might lead to thinking of pg_operator as
an extension of pg_proc in the same way that pg_aggregate is.
But we have not unified pg_aggregate into pg_proc, and I don't
think anyone wants to, because pg_proc rows are undesirably
wide already. There's a similar objection to attaching
optimization fields directly to pg_proc.
You could imagine some follow-on internal cleanup like trying
to unify FuncExpr and OpExpr into a single node type (carrying
a function OID and optionally an operator OID). But that need
not have any user-visible impact either; it'd mainly be good
for eliminating a lot of near-duplicate code.
regards, tom lane
Re: Named Operators
On 27.01.23 16:34, Matthias van de Meent wrote: On Fri, 27 Jan 2023 at 16:26, Peter Eisentraut wrote: On 12.01.23 14:55, Matthias van de Meent wrote: Matter of taste, I guess. But more importantly, defining an operator gives you many additional features that the planner can use to optimize your query differently, which it can't do with functions. See the COMMUTATOR, HASHES, etc. clause in the CREATE OPERATOR command. I see. Wouldn't it be better then to instead make it possible for the planner to detect the use of the functions used in operators and treat them as aliases of the operator? Or am I missing something w.r.t. differences between operator and function invocation? E.g. indexes on `int8pl(my_bigint, 1)` does not match queries for `my_bigint + 1` (and vice versa), while they should be able to support that, as OPERATOR(pg_catalog.+(int8, int8)) 's function is int8pl. I have been thinking about something like this for a long time. Basically, we would merge pg_proc and pg_operator internally. Then, all the special treatment for operators would also be available to two-argument functions. And single-argument functions in case of prefix operators, right? Right. (The removal of postfix operators is helpful to remove ambiguity here.)
Re: Named Operators
On Fri, 27 Jan 2023 at 16:26, Peter Eisentraut wrote: > > On 12.01.23 14:55, Matthias van de Meent wrote: > >> Matter of taste, I guess. But more importantly, defining an operator > >> gives you many additional features that the planner can use to > >> optimize your query differently, which it can't do with functions. See > >> the COMMUTATOR, HASHES, etc. clause in the CREATE OPERATOR command. > > I see. Wouldn't it be better then to instead make it possible for the > > planner to detect the use of the functions used in operators and treat > > them as aliases of the operator? Or am I missing something w.r.t. > > differences between operator and function invocation? > > > > E.g. indexes on `int8pl(my_bigint, 1)` does not match queries for > > `my_bigint + 1` (and vice versa), while they should be able to support > > that, as OPERATOR(pg_catalog.+(int8, int8)) 's function is int8pl. > > I have been thinking about something like this for a long time. > Basically, we would merge pg_proc and pg_operator internally. Then, all > the special treatment for operators would also be available to > two-argument functions. And single-argument functions in case of prefix operators, right?
Re: Named Operators
On 12.01.23 14:55, Matthias van de Meent wrote: Matter of taste, I guess. But more importantly, defining an operator gives you many additional features that the planner can use to optimize your query differently, which it can't do with functions. See the COMMUTATOR, HASHES, etc. clause in the CREATE OPERATOR command. I see. Wouldn't it be better then to instead make it possible for the planner to detect the use of the functions used in operators and treat them as aliases of the operator? Or am I missing something w.r.t. differences between operator and function invocation? E.g. indexes on `int8pl(my_bigint, 1)` does not match queries for `my_bigint + 1` (and vice versa), while they should be able to support that, as OPERATOR(pg_catalog.+(int8, int8)) 's function is int8pl. I have been thinking about something like this for a long time. Basically, we would merge pg_proc and pg_operator internally. Then, all the special treatment for operators would also be available to two-argument functions.
Re: Named Operators
On Fri, Jan 20, 2023 at 9:32 AM Ted Yu wrote: > > Since `validIdentifier` doesn't modify the contents of `name` string, it > seems that there is no need to create `tmp` string in `validNamedOperator`. > You can pass the start and end offsets into the string (name) as second and > third parameters to `validIdentifier`. Thanks for reviewing the patch! I was making a temporary copy of the string, since I had to modify it before the validation, whereas the callee expects a `const char*`. I agree that the same check can be done with more elegance, while eliminating the temporary allocation. Please find the updated patch attached. Instead of passing the start and end of region I want to check, as suggested, I'm now passing just the length of the string I want validated. But I think that's for the better, since it now aligns with the comment that validIdentifier() does not check if the passed string is shorter than NAMEDATALEN. Best regards, Gurjeet http://Gurje.et named_operators_v4.patch Description: Binary data
Re: Named Operators
On Fri, Jan 20, 2023 at 9:17 AM Gurjeet Singh wrote:
> On Sat, Jan 14, 2023 at 6:14 AM Gurjeet Singh wrote:
> >
> > I agree that an identifier _surrounded_ by the same token (e.g. #foo#)
> > or the pairing token (e.g. {foo}) looks better aesthetically, so I am
> > okay with any of the following variations of the scheme, as well:
> >
> > \#foo\# (tested; works)
> > \#foo# (not tested; reduces ident length by 1)
> >
> > We can choose a different character, instead of #. Perhaps \{foo} !
>
> Please find attached the patch that uses \{foo} styled Named
> Operators. This is in line with Tom's reluctant hint at possibly using
> curly braces as delimiter characters. Since the curly braces are used
> by the SQL Specification for row pattern recognition, this patch
> proposes escaping the first of the curly braces.
>
> We can get rid of the leading backslash, if (a) we're confident that
> SQL committee will not use curly braces anywhere else, and (b) if
> we're confident that if/when Postgres supports Row Pattern Recognition
> feature, we'll be able to treat curly braces inside the PATTERN clause
> specially. Since both of those conditions are unlikely, I think we
> must settle for the escaped-first-curly-brace style for the naming our
> operators.
>
> Keeping with the previous posts, here's a sample SQL script showing
> what the proposed syntax will look like in action. Personally, I
> prefer the \#foo style, since the \# prefix stands out among the text,
> better than \{..} does, and because # character is a better signal of
> an operator than {.
>
> create operator \{add_point}
> (function = box_add, leftarg = box, rightarg = point);
> create table test(a box);
> insert into test values('((0,0),(1,1))'), ('((0,0),(2,1))');
> select a as original, a \{add_point} '(1,1)' as modified from test;
> drop operator \{add_point}(box, point);
>
> Best regards,
> Gurjeet
> http://Gurje.et
Hi,
Since `validIdentifier` doesn't modify the contents of `name` string, it
seems that there is no need to create `tmp` string in `validNamedOperator`.
You can pass the start and end offsets into the string (name) as second and
third parameters to `validIdentifier`.
Cheers
Re: Named Operators
On Sat, Jan 14, 2023 at 6:14 AM Gurjeet Singh wrote:
>
> I agree that an identifier _surrounded_ by the same token (e.g. #foo#)
> or the pairing token (e.g. {foo}) looks better aesthetically, so I am
> okay with any of the following variations of the scheme, as well:
>
> \#foo\# (tested; works)
> \#foo# (not tested; reduces ident length by 1)
>
> We can choose a different character, instead of #. Perhaps \{foo} !
Please find attached the patch that uses \{foo} styled Named
Operators. This is in line with Tom's reluctant hint at possibly using
curly braces as delimiter characters. Since the curly braces are used
by the SQL Specification for row pattern recognition, this patch
proposes escaping the first of the curly braces.
We can get rid of the leading backslash, if (a) we're confident that
SQL committee will not use curly braces anywhere else, and (b) if
we're confident that if/when Postgres supports Row Pattern Recognition
feature, we'll be able to treat curly braces inside the PATTERN clause
specially. Since both of those conditions are unlikely, I think we
must settle for the escaped-first-curly-brace style for the naming our
operators.
Keeping with the previous posts, here's a sample SQL script showing
what the proposed syntax will look like in action. Personally, I
prefer the \#foo style, since the \# prefix stands out among the text,
better than \{..} does, and because # character is a better signal of
an operator than {.
create operator \{add_point}
(function = box_add, leftarg = box, rightarg = point);
create table test(a box);
insert into test values('((0,0),(1,1))'), ('((0,0),(2,1))');
select a as original, a \{add_point} '(1,1)' as modified from test;
drop operator \{add_point}(box, point);
Best regards,
Gurjeet
http://Gurje.et
diff --git a/src/backend/catalog/pg_operator.c b/src/backend/catalog/pg_operator.c
index 1017f2eed1..c5b8562cb5 100644
--- a/src/backend/catalog/pg_operator.c
+++ b/src/backend/catalog/pg_operator.c
@@ -31,6 +31,7 @@
#include "catalog/pg_type.h"
#include "miscadmin.h"
#include "parser/parse_oper.h"
+#include "parser/scansup.h"
#include "utils/acl.h"
#include "utils/builtins.h"
#include "utils/lsyscache.h"
@@ -79,6 +80,10 @@ validOperatorName(const char *name)
if (len == 0 || len >= NAMEDATALEN)
return false;
+ /* Is this a Named Operator? */
+ if (validNamedOperator(name))
+ return true;
+
/* Can't contain any invalid characters */
/* Test string here should match op_chars in scan.l */
if (strspn(name, "~!@#^&|`?+-*/%<>=") != len)
diff --git a/src/backend/parser/scan.l b/src/backend/parser/scan.l
index db8b0fe8eb..8587b82c8d 100644
--- a/src/backend/parser/scan.l
+++ b/src/backend/parser/scan.l
@@ -379,6 +379,16 @@ self [,()\[\].;\:\+\-\*\/\%\^\<\>\=]
op_chars [\~\!\@\#\^\&\|\`\?\+\-\*\/\%\<\>\=]
operator {op_chars}+
+/*
+ * Named Operators, e.g. \{foo}
+ *
+ * {namedopfailed*} are error rules to avoid scanner backup when
+ * {namedop} fails to match its trailing tokens.
+ */
+namedop \\\{{identifier}\}
+namedopfailed1 \\\{{identifier}
+namedopfailed2 \\\{
+
/*
* Numbers
*
@@ -768,6 +778,23 @@ other .
}
<> { yyerror("unterminated dollar-quoted string"); }
+{namedop} {
+ SET_YYLLOC();
+ if (yyleng >= NAMEDATALEN)
+ yyerror("operator name too long");
+ /* XXX Should we support double-quoted, case sensitive names? */
+ yylval->str = downcase_identifier(yytext, yyleng, false, false);
+ return Op;
+}
+
+{namedopfailed1} {
+ yyerror("unexpected token");
+}
+
+{namedopfailed2} {
+ yyerror("unexpected token");
+}
+
{xdstart} {
SET_YYLLOC();
BEGIN(xd);
diff --git a/src/backend/parser/scansup.c b/src/backend/parser/scansup.c
index 602108a40f..05c46ae09e 100644
--- a/src/backend/parser/scansup.c
+++ b/src/backend/parser/scansup.c
@@ -125,3 +125,70 @@ scanner_isspace(char ch)
return true;
return false;
}
+
+/*
+ * validNamedOperator() -- return true if name adheres to the scanner rule
+ * {namedop}
+ */
+bool
+validNamedOperator(const char *name)
+{
+ size_t len = strlen(name);
+ bool valid_identifier;
+ char *tmp;
+
+ if (len < 4 || len >= NAMEDATALEN)
+ return false;
+
+ if (name[0] != '\\' || name[1] != '{' || name[len-1] != '}')
+ return false;
+
+ tmp = pstrdup(name);
+
+ // Disregard the delimiters
+ tmp[len-1] = '\0';
+ valid_identifier = validIdentifier(tmp + 2);
+ pfree(tmp);
+
+ return valid_identifier;
+}
+
+/*
+ * validIdentifier() -- return true if name adheres to the scanner rule
+ * {identifier}
+ *
+ * Note: this function does not check if the identifier length
+ * is less than NAMEDATALEN.
+ */
+bool
+validIdentifier(const char *name)
+{
+ uint8 c;
+ size_t i, len = strlen(name);
+
+ // Reject if first character is not part of ident_start
+ c = name[0];
+ if ( !(c == '_'
+ || (c >='A' && c <= 'Z')
+ || (c >='a' && c <= 'z')
+ || (c >= 0200 && c <= 0377)))
+ {
+ return false;
+ }
+
+ // Reject if other characters are not part of
Re: Named Operators
On Thu, Jan 12, 2023 at 5:55 AM Matthias van de Meent
wrote:
> On Thu, 12 Jan 2023 at 11:59, Gurjeet Singh wrote:
> > ... defining an operator
> > gives you many additional features that the planner can use to
> > optimize your query differently, which it can't do with functions. See
> > the COMMUTATOR, HASHES, etc. clause in the CREATE OPERATOR command.
>
> I see. Wouldn't it be better then to instead make it possible for the
> planner to detect the use of the functions used in operators and treat
> them as aliases of the operator? Or am I missing something w.r.t.
> differences between operator and function invocation?
>
> E.g. indexes on `int8pl(my_bigint, 1)` does not match queries for
> `my_bigint + 1` (and vice versa), while they should be able to support
> that, as OPERATOR(pg_catalog.+(int8, int8)) 's function is int8pl.
Such a feature would be immensely useful in its own right. But it's
also going to be at least 2 orders of magnitude (or more) effort to
implement, and to get accepted in the community. I'm thinking of
changes in planner, catalogs, etc.
On Thu, Jan 12, 2023 at 7:21 AM Tom Lane wrote:
> Matthias van de Meent writes:
> > I'm -1 on the chosen syntax; :name: shadows common variable
> > substitution patterns including those of psql.
>
> Yeah, this syntax is DOA because of that. I think almost
> anything you might invent is going to have conflict risks.
I remember discussing this in a meeting with Joe Conway a few weeks
ago, when this was just a proposal in my head and I was just bouncing
it off him. And I remember pointing out that colons would be a bad
choice because of their use in psql; but for life of me I can't think
of a reason (except temporary memory loss) why I failed to consider
the psql conflict when implementing the feature. If only some test in
`make check` would have pointed out the mistake, I wouldn't have made
this obvious mistake.
> We could probably make it work by allowing the existing OPERATOR
> syntax to take things that look like names as well as operators,
> like
>
> expr3 OPERATOR(contains_all) expr4
>
> But that's bulky enough that nobody will care to use it.
+1. Although that'd be better for readers than the all-special-char
names, this format is bulky enough that you won't be able to convince
the query writers to bother using it. But if all other efforts fail,
I'll take this format over the cryptic ones any day.
> On the whole I don't see this proposal going anywhere.
> There's too much investment in the existing operator names,
> and too much risk of conflicts if you try to shorten the
> syntax.
I wouldn't give up on the idea, yet :-) See new proposal below.
On Thu, Jan 12, 2023 at 9:14 AM Tom Lane wrote:
> Isaac Morland writes:
> > What about backticks (`)?
>
> Since they're already allowed as operator characters, you can't
> use them for this purpose without breaking existing use-cases.
>
> Even if they were completely unused, I'd be pretty hesitant to
> adopt them for this purpose because of the potential confusion
> for users coming from mysql.
Since when have we started caring for the convenience of users of
other databases?!! /s
> Pretty much the only available syntax space is curly braces,
> and I don't really want to give those up for this either.
> (One has to assume that the SQL committee has their eyes
> on those too.)
On Thu, Jan 12, 2023 at 9:45 AM Vik Fearing wrote:
> They are used in row pattern recognition.
I was very hopeful of using { }, and hoping that we'd beat the SQL
committee to it, so that they have to choose something else, if we
release this into the wild before them. But it seems that they beat us
to it long ago. (tangent: Reading some blog posts, I have to say I
loved the Row Pattern Recognition feature!)
Considering that there are almost no printable characters left in
1-255 ASCII range for us to choose from, I had to get creative; and I
believe I have found a way to make it work.
Unless the SQL committee has their eyes on a freestanding backslash \
character for something, I believe we can use it as a prefix for Named
Operators. Since the most common use of backslash is for escaping
characters, I believe it would feel natural for the users to use it as
described below.
New scheme for the named operators: \#foo That is, an identifier
prefixed with \# would serve as an operator name. psql considers \ to
be the start of its commands, but it wasn't hard to convince psql to
ignore \# and let it pass through to server.
I agree that an identifier _surrounded_ by the same token (e.g. #foo#)
or the pairing token (e.g. {foo}) looks better aesthetically, so I am
okay with any of the following variations of the scheme, as well:
\#foo\# (tested; works)
\#foo# (not tested; reduces ident length by 1)
We can choose a different character, instead of #. Perhaps \{foo} !
Attached is the v2 patch that supports \#foo style Named Operators.
Following is the SQL snippet to see what the usage looks like.
create
Re: Named Operators
On Thu, Jan 12, 2023 at 10:14 AM Tom Lane wrote: > Isaac Morland writes: > > What about backticks (`)? They are allowed as operator characters but do > > not otherwise appear in the lexical syntax as far as I can tell: > > https://www.postgresql.org/docs/current/sql-syntax-lexical.html > > Since they're already allowed as operator characters, you can't > use them for this purpose without breaking existing use-cases. > > IIUC, specifically the fact that an operator is defined to start with one of those symbols and end at the first non-symbol. We can't change the allowed set of non-symbols at this point, without defining something else to denote the start of an operator. David J.
Re: Named Operators
On 1/12/23 18:14, Tom Lane wrote: Pretty much the only available syntax space is curly braces, and I don't really want to give those up for this either. (One has to assume that the SQL committee has their eyes on those too.) They are used in row pattern recognition. -- Vik Fearing
Re: Named Operators
Isaac Morland writes: > What about backticks (`)? They are allowed as operator characters but do > not otherwise appear in the lexical syntax as far as I can tell: > https://www.postgresql.org/docs/current/sql-syntax-lexical.html Since they're already allowed as operator characters, you can't use them for this purpose without breaking existing use-cases. Even if they were completely unused, I'd be pretty hesitant to adopt them for this purpose because of the potential confusion for users coming from mysql. Pretty much the only available syntax space is curly braces, and I don't really want to give those up for this either. (One has to assume that the SQL committee has their eyes on those too.) regards, tom lane
Re: Named Operators
On Thu, 12 Jan 2023 at 05:59, Gurjeet Singh wrote: I'll consider using one of the other special characters. Do you have > any suggestions? > What about backticks (`)? They are allowed as operator characters but do not otherwise appear in the lexical syntax as far as I can tell: https://www.postgresql.org/docs/current/sql-syntax-lexical.html
Re: Named Operators
On Thu, Jan 12, 2023 at 3:59 AM Gurjeet Singh wrote: > On Thu, Jan 12, 2023 at 1:49 AM Matthias van de Meent > wrote: > > > I'm -1 on the chosen syntax; :name: shadows common variable > > substitution patterns including those of psql. > > I'll consider using one of the other special characters. Do you have > any suggestions? > > The R language uses %...% to denote custom operators. That would be a bit annoying for dynamic SQL using format though... Do we have to choose? There are 15 allowed characters for operator names presently (aside from + and -), could we define the rule that an operator name can contain any sequence of alphabetic+underscore+space? characters so long as the first and last symbol of the operator name is one of those 15 characters? Another appealing option would be the non-matching but complementary pair <...> (I'd consider removing these from the 15 choices in we go that route) SELECT 1 2; I would probably avoid requiring back-ticks given their usage as identifier quoting in other systems - probably remove it from the 15 choices if we go that route. David J.
Re: Named Operators
Matthias van de Meent writes: > I'm -1 on the chosen syntax; :name: shadows common variable > substitution patterns including those of psql. Yeah, this syntax is DOA because of that. I think almost anything you might invent is going to have conflict risks. We could probably make it work by allowing the existing OPERATOR syntax to take things that look like names as well as operators, like expr3 OPERATOR(contains_all) expr4 But that's bulky enough that nobody will care to use it. On the whole I don't see this proposal going anywhere. There's too much investment in the existing operator names, and too much risk of conflicts if you try to shorten the syntax. regards, tom lane
Re: Named Operators
On Thu, 12 Jan 2023 at 11:59, Gurjeet Singh wrote: > > On Thu, Jan 12, 2023 at 1:49 AM Matthias van de Meent > wrote: > > > > On Thu, 12 Jan 2023 at 10:16, Gurjeet Singh wrote: > > > > > > Technically correct name of this feature would be Readable Names for > > > Operators, or Pronounceable Names for Operators. But I'd like to call > > > it Named Operators. > > > > > > With this patch in place, the users can name the operators as > > > :some_pronounceable_name: instead of having to choose from the special > > > characters like #^&@. > > > [...] > > > I think Named Operators will significantly improve the readability > > > of queries. > > > > Couldn't the user better opt to call the functions that implement the > > operator directly if they want more legible operations? So, from your > > example, `SELECT box_add(a, b)` instead of `SELECT a :add_point: b`? > > Matter of taste, I guess. But more importantly, defining an operator > gives you many additional features that the planner can use to > optimize your query differently, which it can't do with functions. See > the COMMUTATOR, HASHES, etc. clause in the CREATE OPERATOR command. I see. Wouldn't it be better then to instead make it possible for the planner to detect the use of the functions used in operators and treat them as aliases of the operator? Or am I missing something w.r.t. differences between operator and function invocation? E.g. indexes on `int8pl(my_bigint, 1)` does not match queries for `my_bigint + 1` (and vice versa), while they should be able to support that, as OPERATOR(pg_catalog.+(int8, int8)) 's function is int8pl. Kind regards, Matthias van de Meent
Re: Named Operators
On Thu, Jan 12, 2023 at 1:49 AM Matthias van de Meent wrote: > > On Thu, 12 Jan 2023 at 10:16, Gurjeet Singh wrote: > > > > Technically correct name of this feature would be Readable Names for > > Operators, or Pronounceable Names for Operators. But I'd like to call > > it Named Operators. > > > > With this patch in place, the users can name the operators as > > :some_pronounceable_name: instead of having to choose from the special > > characters like #^&@. > > [...] > > I think Named Operators will significantly improve the readability > > of queries. > > Couldn't the user better opt to call the functions that implement the > operator directly if they want more legible operations? So, from your > example, `SELECT box_add(a, b)` instead of `SELECT a :add_point: b`? Matter of taste, I guess. But more importantly, defining an operator gives you many additional features that the planner can use to optimize your query differently, which it can't do with functions. See the COMMUTATOR, HASHES, etc. clause in the CREATE OPERATOR command. https://www.postgresql.org/docs/current/sql-createoperator.html This proposal is primarily a replacement for the myriad of hard-to-pronounce operators that users have to memorize. For example, it'd be nice to have readable names for the PostGIS operators. https://postgis.net/docs/reference.html#Operators For someone who's reading/troubleshooting a PostGIS query, when they encounter operator <<| — in the query for the first time, they'd have to open up the docs. But if the query used the :strictly_below: operator, there's no need to switch to docs and lose context. > I'm -1 on the chosen syntax; :name: shadows common variable > substitution patterns including those of psql. Ah, thanks for reminding! Early on when I hadn't written code yet, I remember discarding colon : as a delimiter choice, precisely because it is used for using variables in psql, and perhaps in some drivers, as well. But in the rush of implementing and wrangling code, I forgot about that argument altogether. I'll consider using one of the other special characters. Do you have any suggestions? Best regards, Gurjeet http://Gurje.et
Re: Named Operators
On Thu, 12 Jan 2023 at 10:16, Gurjeet Singh wrote: > > Technically correct name of this feature would be Readable Names for > Operators, or Pronounceable Names for Operators. But I'd like to call > it Named Operators. > > With this patch in place, the users can name the operators as > :some_pronounceable_name: instead of having to choose from the special > characters like #^&@. > [...] > I think Named Operators will significantly improve the readability > of queries. Couldn't the user better opt to call the functions that implement the operator directly if they want more legible operations? So, from your example, `SELECT box_add(a, b)` instead of `SELECT a :add_point: b`? I'm -1 on the chosen syntax; :name: shadows common variable substitution patterns including those of psql. Kind regards, Matthias van de Meent
Re: Named Operators
Please see attached a slightly updated patch. There were some comment changes sitting in uncommitted in Git worktree, that were missed. Best regards, Gurjeet http://Gurje.et named_operators_v1.patch Description: Binary data
