>
Tom is probably on the right track here with psql versions.Yes!
> I still say this shouldn't work per the documentation since "return" isn't a valid SQL statement,
Right you are. Just looked at my most common use, and it's returning a SELECT. I write short functions and procedures by hand, but mostly I write code generators to punch them out for me. In my most common case for BEGIN ATOMIC, I've got a setup like this:
* Create a view, which creates a compound type by proxy.
* Create a function that takes an array of my_foo_v1[].
* From the client side, package data formatted according to the rules of the my_foo_v1 format.
* Submit those arrays to insert_my_foo_v1(my_foo_v1[])
That function unnests the data into an in-memory row set, then INSERT...ON CONFLICTs everything into the target table. I'm trying to convert to PG 15 now, as we have some cases where we'll need MERGE instead. (Specifically, when we want to maintain a globally unique ID across partitions divided by date.)
Each field appears 2-3 times in the generated function in the extraction, insert, and on conflict clauses.....3 times for on conflict, I think that it will only be twice for MERGE, haven't written that yet past the toy stage.
So, you can see why I appreciate BEGIN ATOMIC. And, for the ON CONFLICT version, the return is a small object including the submitted row count, inserted row count, and estimated update count. MERGE doesn't support RETURNING, so I'll have to live without it.
RETURNING is such a gem, it ought to be added to the standards.
If you're wondering "why go to all that work?", it's so that we can support multiple format versions on inserts on a table simultaneously. Because lagging clients. I machine-generate it, so it doesn't take long at all, once the generator is written. Plus, I'm a fanatic for type checking. Postgres' type features are absolutely fantastic, spoiling me forever for other databases.. I assume that other people have ORMs to do this sort of magic for them, but I didn't go that way.
