Re: psql 14.7/15.2 report a bogus syntax error on function and procedure files that use BEGIN ATOMIC

> 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.

On Mon, Mar 13, 2023 at 4:04 PM David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Sun, Mar 12, 2023 at 9:57 PM David Adams <dpadams(at)gmail(dot)com> wrote:
>
>> Thanks for the answer, a user error would be best case for me as then I
>> can fix it.
>>
>
> Tom is probably on the right track here with psql versions.
>
> I still say this shouldn't work per the documentation since "return" isn't
> a valid SQL statement, if you want to use "begin atomic" write "SELECT
> 'result';" instead as the final statement of the function. The "return"
> syntax is shown to only work with the "LANGUAGE SQL RETURN expression;"
> format. Though since this does in fact work the docs probably should be
> tweaked instead.
>
> David J.
>
>