-- No error do $body$ declare p int not null := 0; begin for p in ( select pid from pg_stat_activity where backend_type = 'client backend' and pid <> pg_backend_pid()) loop perform pg_terminate_backend(p); end loop; end; $body$;
While your script works great, I'm wondering why you don't write it this way:
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE backend_type = 'client backend' AND pid <> pg_backend_pid();
As it is less code, it's quicker to understand what it does.
Well, yes… I have often been accused of being anally fixated on details—and of verbosity. This is just the old chestnut that a "select" statement shouldn't have side effects.
Oh, OK, looks like a good reason to me. I will probably still do the quick SELECT, but I understand your view on it.
"pg_terminate_backend(p)" ought, by the book, to be a procedure. But I suppose that it dates from the time when PG had only user-defined functions (and no shipped procedures). And "perform" makes a function feel to me to be a bit more like a procedure than just selecting it feels. Others might well disagree…