> > -- this is how I fake an 'exit 0' now: > \set work_needs_to_be_done t > select > case > when :'work_needs_to_be_done'::boolean then '' > else '\q' > end as cmd > \gset > :cmd > > -- ridiculous example to illustrate complications in remembering past > commands > select > case > when random() < 0.5 then '\ir my_script.sql' > when random() < 0.7 'select * from a_table; select count(*) from > another_table;' > else 'select null as foo;' > end as cmd > \gset > :cmd > > And even then, things get complicated, because an \ir include which makes it > this iteration might not make it the next, and the \endwhile might have been > inside that include, or vice-versa, an included file starts a \while it > doesn't finish.
I see your point. Just out of curiosity, why in the world don't you use something other than psql for scripting? I mean, if you accessed the data from Perl or Python or $INSERT_YOUR_FAVORITE_SCRIPTING_LANGUAGE_HERE, you could do all of this stuff very easily without any contortions. I've always thought of psql as something that's fine for interactive use and goofy trivial scripting but not really suitable for serious work. I grant that you seem to be making it serve the purpose, but, man.
Since you asked:
Heh. I *don't* do the second example, I was just pointing out that those things could be done, not that they should be done, and how hard it would be to implement loops when the source code is potentially coming from a stream.
My current client does use mostly python, but also perl, and ruby, and PHP and, reactjs, and $NEW_THING.
Here are the reasons I often prefer psql: - Wiring up a python script to do one if-then in the middle of 40 SQL statements goes a long way toward obfuscating what SQL is going to be run.
- Packaging up the SQL statements in a DO $$ $$; block conceals what statements were run, and how long they took.
- In python, etc, it's up to me to show rowcounts and timings.
- On very small docker-ish systems, the fewer things I have to install, the better, and golly, python is large these days.
- When doing work for regulated industry clients (SOX,HIPAA, school district PII, etc), the auditors like seeing clearly what SQL _will_ run, what SQL _did_ run, and what was affected. psql scripts with echo-queries set and captured output do that nicely. Installing extra scripting languages gives them the vapors, and now we need an auditor that thinks they know two languages, not one. I'm not saying it makes sense, I'm saying fewer dependencies gets a auditor's checkbox checked sooner.
Right, I think that while/for can be left for another time, as long as the plan doesn't preclude doing it someday.