Re: Syntax checking DO blocks and ALTER TABLE statements?

David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> writes:

> On Tue, Feb 16, 2021 at 3:43 PM Ron <ronljohnsonjr(at)gmail(dot)com> wrote:
>
>>
>> How does one go about syntax checking this?
>>
>> (There are 222 ALTER TABLE ADD FOREIGN KEY statements that I'm wrapping in
>> similar DO blocks, and want to make sure the statements are clean.)
>>
>>
> Begin a transaction, execute the DO, capture an error if there is one,
> rollback the transaction.
>

As David points out, wrapping the whole thing in a transaction will at
least guarantee it all succeeds or it is all rollled back. This can be
frustrating if the statements are slow and there are a lot of them as it
can result in a very tedious do-run-fix cycle.

Something which can help is using an editor with good font highlighting
and parsing support. One interesting area I've not yet looked at is the
development of LSP (Language Server Protocol) servers for SQL. I've used
LSP for other languages with great success. The challenge with databases
is that there is enough variation between different vendor
implementations to make accurate parsing and validation tedious to
implement, so most solutions only focus on ANSI compliance. Still, that
can be very useful.

See https://github.com/lighttiger2505/sqls for one example of an LSP
server for SQL and https://microsoft.github.io/language-server-protocol/
for more background on LSP and what it can provide. Many editors,
including VSCode, VI, Emacs, TextMate etc now have some support for LSP.

--
Tim Cross