On Tue, Feb 16, 2021 at 4:28 PM Tim Cross <theophilusx@xxxxxxxxx> wrote:
David G. Johnston <david.g.johnston@xxxxxxxxx> writes:
> On Tue, Feb 16, 2021 at 3:43 PM Ron <ronljohnsonjr@xxxxxxxxx> 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.
