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 habropolisters know, for the design of the source code a special tag is used <source>, which highlights the syntax. It is also no secret that the backlighting does not always turn out to be perfect, and then the authors (who care how their articles look) are forced to engage in amateur performances — color their code with help <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 daleraliyorovprompted a way out: add PostgreSQL support to the highlightjs library used by Habr. 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 and in markdown - like this: 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

<source lang="pgsql">
мой код
</source>



```pgsql
мой код
```


CREATETABLE aircrafts_data (
    aircraft_code character(3) NOTNULL,
    model jsonbNOTNULL,
    range integerNOTNULL,
    CONSTRAINT aircrafts_range_check CHECK ((range > 0))
);

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

<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>


CREATEFUNCTION get_available_flightid(date) RETURNSSETOFintegerAS $$BEGINRETURN QUERYSELECT flightid FROM flight WHERE flightdate >= $1AND flightdate < ($1 + 1);
  IFNOTFOUNDTHENRAISEEXCEPTION'Нет рейсов на дату: %.', $1;
  ENDIF;
  RETURN;
END
$$LANGUAGE plpgsql;

write small subtlety lies in the fact that the character strings enclosed in dollars, have always highlighted as a code, and the strings are not highlighted in apostrophes ever . 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:

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


CREATEFUNCTION perl_max (integer, integer) RETURNSintegerAS $$my ($x, $y) = @_;
    if (notdefined $x) {
        returnundefifnotdefined $y;
        return $y;
    }
    return $x ifnotdefined $y;
    return $x if $x > $y;
    return $y;
$$LANGUAGE plperl;

To do this, you do not need anything special, just write. Of course, the chosen language depends only on what is written inside the dollars, and is not determined in any way by what is written after LANGUAGE. In general, the backlight corresponds to the recently released 11th version of PostgreSQL. There was a lot of doubt about the feature highlights. 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.

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


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 ($$<examplexmlns="http://example.com/myns"xmlns:B="http://example.com/b"><itemfoo="1"B:bar="2"/><itemfoo="3"B:bar="4"/><itemfoo="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 intPATH'@foo',
                  bar intPATH'@B:bar');

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

we write 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!

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