Get the list of PostgreSQL statements

Intro

Ah, for a long time I did not take checkers. But there is nothing to do, I am locked in a strange city until the evening and the only consolation for me is non-alcoholic beer in a local pizzeria. Therefore, in order to slightly kill time, I will write an article about how I solved the simple task of our front tips.

They are currently creating an interface, in which, among other things, there will be a filter editor for the returned data. That's why they asked me:

will it be enough to use standard "<,>,!=,=" as comparison operators? Or are there any operators worth adding?

“No, not enough,” I answered them. - Not only does PostgreSQL out of the box support the dark-tingmly operators , they can also be determined by themselves, and in addition each extension tries to supplement the system with another dozen.

My answer puzzled them. And when someone is puzzled, he will puzzle his neighbor with pleasure. And they asked me (as an indisputable authority, of course) to compile a list of operators, at least for the case of a naked and fresh system.

But I am not the first time married, and I know that the system is different from the system, starting with the differences between the versions of vanilla elephant and ending with forks, for example PostgresPro, CitusDB ... thousands of them. Well, and secondly, these same guys will want to get a list of available operators for display tomorrow, and I still have to solve this problem. Because this text represents the course of my thoughts.
')

Formulation of the problem

1. We need operators only for the filter, hence the result of applying the operator will be a boolean type
2. About binary or unary nothing is said, then we take everything.

Decision

My first desire was to go into the manual and pull in a quick list of the main operators from there. Lazy and not sports.

Then she visited the idea to go through the source code on this subject. It lies good in /src/include/catalog/pg_operator.h . Naturally, the thought also went to the forest. Since we will dig into the system directories of the server, it is more convenient to do this using SQL.

In the pizzeria, there was no Postgres server raised, but I had with me, as they say, it was. If you don’t have one with you, then you can do these experiments with me on the wonderful SQL Fiddle service.

So, information about the operators is stored in the system catalog pg_operator , because the first iteration is logical to assume

 SELECT * FROM pg_operator; 

On my bare PostgreSQL 9.6, I received 772 rows, and on the same SQL Fiddle (PostgreSQL 9.3) request returned 823 rows. Such a spread should not surprise you. It turned out that in the second case “out of the box” an additional 8 extensions were installed:

 SELECT extname FROM pg_extension; 

 | extname | |---------------| | plpgsql | | fuzzystrmatch | | hstore | | intagg | | intarray | | ltree | | pg_trgm | | uuid-ossp | | xml2 | 

First approach

Now we restrict the output to only operators that return a boolean result.

 SELECT * FROM pg_operator WHERE oprresult = 'boolean'::regtype; --------------- Record Count: 513; (local 9.6) Record Count: 552; (SQL Fiddle 9.3) 

Let us examine this particular street magic. The oprresult field is of type oid, which from the inside is an unsigned integer type of 4-bytes. In an amicable way, it would be necessary to compare it with a number, but not all such nerds like me, to remember that the boolean type oid is 16. Therefore, we used the pseudotype regtype and type casting. Very handy little thing. I advise you to adopt.

We will cut

As you probably noticed, the same operator can be used for different types of arguments. We do not need duplicates in total, so we will try to remove duplicates and make the conclusion more meaningful.

 SELECT oprname, count(1), array_agg(DISTINCT oprleft::regtype) AS left_args, array_agg(DISTINCT oprright::regtype) AS right_args, array_agg(DISTINCT obj_description(oid, 'pg_operator')) AS comments FROM pg_operator WHERE oprresult = 'boolean'::regtype GROUP BY oprname ORDER BY count(1) DESC; --------------- Record Count: 58; (local 9.6) Record Count: 62; (SQL Fiddle 9.3) 

Total

As you can see in PostgreSQL, there are about 60 different (sic!) Operators that return a logical type and are suitable for building filters. Such a variety is easy to explain if we recall that PostgreSQL has a flexible typing system. Therefore, many additional operators are designed to facilitate the work with user-defined types. And their number will undoubtedly only grow.