Get a list of PostgreSQL statements

Intro

Oh, for a long time I did not pick up the checkers. But there is nothing to do, I was locked in a strange city until the evening and the only consolation is non-alcoholic beer in a local pizzeria. Therefore, in order to slightly kill time, I’ll write an article about how I solved the simple problem of our front ends (front-end).

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

will standard ones be enough 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 darkness of operators , so you can still define them yourself, and in addition, each extension strives to add a dozen more to the system.

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

But this is not the first time I’ve been married, and I know that the system is different, starting with the differences between the versions of the vanilla elephant and ending with forks, such as PostgresPro, CitusDB ... thousands of them. Well, and secondly, these same guys will want to get a list of available operators for display tomorrow, and yet I still have to solve this problem. Therefore, this text is a train of my thoughts.

Formulation of the problem

1. We need operators only for the filter, therefore, the result of applying the operator will be a logical type boolean
2. Nothing is said about binary or unary, so we take everything.

Decision

My first desire was to get into the manual and pull out a list of the main operators quickly from there. Lazy and not athletic.

Then I visited the idea of ​​scrolling the source for this subject. This good lies in /src/include/catalog/pg_operator.h . Naturally, the thought also went into the woods. Since we delve into the system directories of the server, it is more convenient to do this using SQL.

The pizzeria didn’t have the Postgres server raised, but I had it with me, as they say. If you don’t have it with you, then you can conduct these experiments with me on the wonderful SQL Fiddle service .

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

SELECT * FROM pg_operator;

On my bare PostgreSQL 9.6, I received 772 rows, and on the same SQL Fiddle (PostgreSQL 9.3), the query 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 only to operators who return the result boolean.

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

Let's take a look at this special street magic. The field oprresultis of type oid, which internally is an unsigned 4-byte integer type. In a good way, it would be necessary to compare with a number, but not all such nerds like me, in order to remember that the oid of type boolean is 16. Therefore, here we used the pseudo-type regtypeand type casting. Very convenient 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 about 60 different (sic!) Operators that return a boolean type and are suitable for constructing filters. This variety is easy to explain if you recall that PostgreSQL has a flexible typing system. Therefore, many additional operators are designed to facilitate the work with user types. And their number will undoubtedly only grow.