On Tue, Feb 16, 2021 at 4:28 PM Tim Cross <theophil...@gmail.com> wrote:
> > David G. Johnston <david.g.johns...@gmail.com> writes: > > > On Tue, Feb 16, 2021 at 3:43 PM Ron <ronljohnso...@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. David J.
