Re: proposal - assign result of query to psql variable

I wrote:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>> [ gset_13.diff ]

> One more gripe is that the parsing logic for \gset is pretty
> unintelligible.

After further thought, it seems to me that the problem here is an
overcomplicated definition of the \gset command; it could be made
both more usable and simpler to implement, if we looked at it
differently.

First off, why is there a provision to omit variable names for some
columns, ie why bother with saying that you can write \gset x,,y to
store only the first and third columns? If you didn't want the second
value, why didn't you leave it out of the SELECT to start with?
It seems like the only possible reason for that is if you were lazy
and typed "SELECT *" instead of listing the columns ... but then you
still need to list the columns in \gset, and it's pretty error-prone
to make sure that the \gset variable list lines up with what "*" will
emit.

In fact, it's pretty error-prone to have to make the \gset variable list
line up with the SELECT columns in any case. So here's my proposal:
let's forget the variable list entirely, and use the column names
returned by the server as the variable names to assign to. So instead
of
select 1, 2 \gset x,y
you would write
select 1 as x, 2 as y \gset
or just
select 1 x, 2 y \gset
which is exactly as much typing as the existing definition, but much
harder to screw up by misaligning the SELECT's values with the target
names. It also makes it really trivial to do the "SELECT *" case ---
you just do it, and ignore any variables for columns you don't care
about.

A probably-useful extension to this basic concept is to allow \gset
to specify an optional prefix, that is
select 1 as x, 2 as y \gset p_
would set p_x and p_y. This would make it easier to manage results from
multiple \gset operations, and to be sure that you didn't accidentally
overwrite some built-in variable.

So this seems to me to be easier and less error-prone to use than the
existing definition. It would also take a lot less code to implement,
since the parsing logic for \gset would reduce to a couple of lines,
and you'd not need the variable-name-list data structure at all.

regards, tom lane