Interesting Bits of Postgres Grammar

4 months ago 5

Hostinger Web Hosting

I’ve been working on Squawk for a while, it’s a linter for PostgreSQL, and it now uses a handmade parser.

So let’s explore some interesting bits from the Postgres grammar.

Custom Operators

Very few operators are defined in the grammar itself and lots of Postgres features rely on custom operators.

For example, Postgres uses <-> for comparing geometric types, along with a whole host of others: ##, @-@, #, @>, <@>, &<, &>, |>>, |<<, <^, >^, ?-, ?|, ?||, ~=.

Note: custom operators can be prefix or infix, but not postfix.

A neat consequence of custom operators is that the following lambda expression syntax from Trino parses natively in Postgres:

select array_filter( array[1, 2, 2, 3], e -> (e % 2) = 0 );

Albiet with the wrong precedence.

Precedence with Compound selects

The following:

select foo union select bar order by baz;

Parses the same as:

(select foo union select bar) order by baz;

Meaning the order by clause is applied to the overall compound select.

Percent Types

With create function, you can specify types based on a table’s column type.

For example, using the type of column c on table t:

create function f(a t.c%type) as 'select 1' language plpgsql;

String Continuation

If you have two string literals separated by new lines, then they’ll be merged together:

returns 'foobar'

But if there’s a comment in between, it’s a syntax error:

select 'foo' /* hmm */ 'bar';

Query 1 ERROR at Line 2: : ERROR: syntax error at or near "'bar'" LINE 2: 'bar'; ^

This behavior is part of the SQL standard.

Quoted Idents

In Postgres you can optionally quote your identifiers, so the following are the same:

select * from t; select * from "t";

If you want to include a double quote in your identifier name, you can escape it like so:

select * from "foo "" bar"

which will select from the foo " bar table.

Unicode Escapes

You can also prefix quoted identifiers with U& and pass unicode escape codes:

-- `74` is the unicode number for `t` select * from U&"\0074";

And if you want to change the escape character from \, you can write:

-- `74` is the unicode number for `t` select * from U&"!0074" uescape '!';

Operator Function

Instead of using an operator directly:

You can use the operator function:

Which allows specifying the operator’s schema:

select 1 operator(pg_catalog.+) 2;

It also works as a prefix operator:

Conclusion

Overall, I think custom operators diverge the most from other mainstream languages and can be tricky to implement.

Read Entire Article

Hostinger Web Hosting