I'm an experienced programmer but really new to SQL,
and I'm wanting a pointer to "the SQL way" to structure/organise chunks of code.
A while back, I wrote a chunk of SQL to Do Something Useful.
I put it in a file (do-something-useful.sql).
And, to protect against getting into a weird state, I wrapped the code in my file with
BEGIN;
UPDATE....
DELETE...
COMMIT;
With the idea that I can do
psql my_database
\i do-something-useful.sql
And be sure that either my task will be have been completed, or nothing with have changed.
NOW, I want to do the same for a BIGGER task.
BUT I realise that if I create bigger-task.sql like this...
BEGIN;
<<preparatory operations>>
\i do-something-useful.sql
<<tidy up code>>
COMMIT;
...the COMMIT inside "do-something-useful.sql" closes the transaction started in "bigger-task.sql"
So I can get some things committed even if other things (in tidy-up) fail.
So how SHOULD I tackle this?
PostgreSQL does not do nested transactions (right?)
So how should I structure my chunks of SQL so that I can have "safe" (all-or-nothing) blocks,
AND use them from within one another?
Robert