{"id":13526,"date":"2024-07-29T16:00:55","date_gmt":"2024-07-29T14:00:55","guid":{"rendered":"https:\/\/www.salvis.com\/blog\/?p=13526"},"modified":"2024-07-29T16:11:16","modified_gmt":"2024-07-29T14:11:16","slug":"islandsql-final-episode-10-parsing-pl-pgsql","status":"publish","type":"post","link":"https:\/\/www.salvis.com\/blog\/2024\/07\/29\/islandsql-final-episode-10-parsing-pl-pgsql\/","title":{"rendered":"IslandSQL Final Episode 10: Parsing PL\/pgSQL"},"content":{"rendered":"\n

Introduction<\/h2>\n\n\n\n

IslandSQL is a parser for SQL files targeting OracleDB or PostgreSQL. The parser is available on Maven Central<\/a> and can process SQL*Plus, SQLcl or psql statements besides SQL statements. However, the focus is on statements with static DML statements and code in PL\/SQL and PL\/pgSQL. For static code analysis, for example.<\/p>\n\n\n\n

In PostgreSQL create function<\/code>, create procedure<\/code> and do<\/code> accept code as a string. This simplifies parsing and the implementation of additional languages. As a result, you can write functions and procedures in SQL, PL\/pgSQL, PL\/Tcl, PL\/Perl and PL\/Python in any standard PostgreSQL distribution.<\/p>\n\n\n\n

Starting with IslandSQL version 0.10 it’s possible to parse SQL and PL\/pgSQL in strings and extend the parse tree accordingly. In this blog post, I will explain how this works.<\/p>\n\n\n\n

This\u00a0VS Code extension<\/a>\u00a0uses IslandSQL in a language server to report syntax errors and to produce parse trees as shown in this blog post.<\/p>\n\n\n\n

PL\/pgSQL as String<\/h2>\n\n\n\n

Let’s look at a do<\/code> statement executing a PL\/pgSQL block provided as a string.<\/p>\n\n\n\n

1) hello_world.sql<\/span><\/path><\/path><\/svg><\/span>
do <\/span>'<\/span><\/span>\nbegin<\/span><\/span>\n   raise notice $$Hello World!$$;<\/span><\/span>\nend<\/span><\/span>\n'<\/span>;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
<\/path><\/path><\/svg><\/span>
NOTICE:  Hello World!<\/span><\/span>\nDO<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
The parse tree in IslandSQL version 0.9 looks as follows:<\/p>\n\n\n\n
\"Parse<\/a><\/figure>\n\n\n\n
Look at the parse tree. It is quite simple. Interesting is the PL\/pgSQL block. The content in single quotes is represented as a single token. The big rectangle at the bottom. It’s a single token regardless of the code size.<\/p>\n\n\n\n
PL\/pgSQL as Subtree<\/h2>\n\n\n\n
Parsing PL\/pgSQL as a string is easy. The lexer produces the token for the string containing the PL\/pgSQL code. The parser does not need to understand the PL\/pgSQL at all.<\/p>\n\n\n\n
But this does not help analyse the PL\/pgSQL code. We need a parser that <\/p>\n\n\n\n