> On Tue, Feb 16, 2021 at 3:43 PM Ron <ronljohnsonjr@gmail.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.
I do presume that someone wanting to test their code in this manner would be doing so in a test environment and an empty database. Which makes the execution time very small.
I personally would also solve the "lot of them" problem by using dynamic SQL, so one pretty much only has to test the code generator instead of all the actual executions - which can simply be confirmed fairly quickly once on a test database without the need for transactions.