PostgreSQL syntax highlighting

I hasten to share the good news: the life of the authors of articles on PostgreSQL and their readers has become a little better.

As all habrifisers know, for the design of the source code a special <source> tag is used, which highlights the syntax. It's also no secret that the backlighting does not always turn out to be perfect, and then the authors (who cares how their articles look) are forced to engage in amateur activities - color their code with <font color=...> .

Everything was especially sad with PostgreSQL, since the backlight covered more or less standard SQL and categorically did not understand the specifics of our DBMS. As time went by, Alexei boomburum diligently fixed my fonts on the source (and I back) until it became obvious that the backlight had to be repaired. Finally, Daler daleraliyorov prompted a way out: add PostgreSQL support to the highlightjs library that Habr uses. And now - ready, welcome.

pgsql: SQL, PL / pgSQL and all-all

So, the secret of the correct highlighting is in the new pgsql language. You can select it in the menu (the “source code” button) or specify it manually. In html for this you need to write
')
<source lang="pgsql">

</source>

and in markdown - like this:

```pgsql

```

In principle, highlightjs can determine the language itself, but normally it works only for large code fragments; on small pieces, autodetection often misses. In addition, autodetection takes time, so if you specify the language explicitly, the code will quickly play with colors.

For example, to get

 CREATE TABLE aircrafts_data ( aircraft_code character(3) NOT NULL, model jsonb NOT NULL, range integer NOT NULL, CONSTRAINT aircrafts_range_check CHECK ((range > 0)) ); 

we are writing

<source lang="pgsql">
CREATE TABLE aircrafts_data (
aircraft_code character(3) NOT NULL,
model jsonb NOT NULL,
range integer NOT NULL,
CONSTRAINT aircrafts_range_check CHECK ((range > 0))
);
</source>

The same pgsql language also colors PL / pgSQL code. For example, to get

 CREATE FUNCTION get_available_flightid(date) RETURNS SETOF integer AS $$ BEGIN RETURN QUERY SELECT flightid FROM flight WHERE flightdate >= $1 AND flightdate < ($1 + 1); IF NOT FOUND THEN RAISE EXCEPTION '   : %.', $1; END IF; RETURN; END $$ LANGUAGE plpgsql; 

write

<source lang="pgsql">
CREATE FUNCTION get_available_flightid(date) RETURNS SETOF integer AS $$
...
$$ LANGUAGE plpgsql;
</source>

A small subtlety is that character strings enclosed in dollars are always highlighted as code, and lines in apostrophes are never highlighted. I considered various options, but this one seemed to be the most adequate.

The ability of highlightjs to automatically determine the language of a fragment allows highlighting functions in other languages. For example, everything will work for PL / Perl:

 CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$ my ($x, $y) = @_; if (not defined $x) { return undef if not defined $y; return $y; } return $x if not defined $y; return $x if $x > $y; return $y; $$ LANGUAGE plperl; 

You do not need anything special, just write

<source lang="pgsql">
CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$
...
$$ LANGUAGE plperl;
</source>

Of course, the chosen language depends only on what is written inside the dollars, and is in no way determined by what is written after LANGUAGE.

In general, the backlight corresponds to the recently released 11th version of PostgreSQL.

There were many doubts about the highlighting of functions. Unfortunately, to distinguish the name of a function from, for example, the name of a table, a full-fledged syntactic analysis is needed, and this is not solved within the framework of syntax highlighting. You can make a long list of standard functions and color them, but what about the functions of the many extensions? As a result, I decided not to color at all - all the same, everything rests on the keywords, but the variegation diminished.

plaintext: text, just text

Sometimes the article is required to issue the results of the query. Of course, there are no keywords there, you don't need to highlight anything, but you want the text to look “console”, just like code. To do this, you can now use the special language plaintext . For example, to get

 WITH xmldata(data) AS (VALUES ($$ <example xmlns="http://example.com/myns" xmlns:B="http://example.com/b"> <item foo="1" B:bar="2"/> <item foo="3" B:bar="4"/> <item foo="4" B:bar="5"/> </example>$$::xml) ) SELECT xmltable.* FROM XMLTABLE(XMLNAMESPACES('http://example.com/myns' AS x, 'http://example.com/b' AS "B"), '/x:example/x:item' PASSING (SELECT data FROM xmldata) COLUMNS foo int PATH '@foo', bar int PATH '@B:bar'); 

  foo | bar -----+----- 1 | 2 3 | 4 4 | 5 (3 rows) 

write

<source lang="pgsql">
WITH xmldata(data) AS (VALUES ($$
...
</source>
<source lang="plaintext">
foo | bar
-----+-----
1 | 2
3 | 4
4 | 5
(3 rows)
</source>

Plaintext should always be specified explicitly, it is not automatically detected.

I hope that the innovation you like and come in handy. If you find an error in how the code is highlighted (and errors are unavoidable, too context-sensitive syntax in SQL), create a task on the project github , and even better - suggest a solution.

PS Do not forget about the PGConf conference, which will be held February 4-6 in Moscow. Applications for reports are accepted until December 5!