Re: proposal - assign result of query to psql variable

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, David Fetter <david(at)fetter(dot)org>
Cc: Shigeru Hanada <shigeru(dot)hanada(at)gmail(dot)com>, Phil Sorber <phil(at)omniti(dot)com>, Erik Rijkers <er(at)xs4all(dot)nl>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: proposal - assign result of query to psql variable
Date: 2013-01-26 17:26:37
Message-ID: CAFj8pRDd5pATnOSBAp=eGt-mF73ku9jszcE3N5tUhR8piBDNCw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello

2013/1/26 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> 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.

possibility to skip some variables is David Fetter's idea. I see only
one possible use case - it enable use some query from history without
necessity to modify query or creating some auxiliary variables.
Personally, I can live without this feature, but it question for David
mainly.

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

hard to say

your proposal has advantages - and implementation is simple, but it is
looking little bit strange - but like other psql features.

I have no strong opinion - I prefer original design, as more explicit
with clean separation line between query and console part, but I am
able to see advantages of your proposal - so depends what will speak
others. I have no problem with your design, although I am thinking so
original design is little bit more safer (but not with significant
difference).

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

I understand to motivation - but I am not enthused. Now - a work with
variables is strange - and with it will be more stranger.

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

I will waiting for others - I can live with this proposal.

Regards

Pavel

>
> regards, tom lane

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Pavel Stehule 2013-01-26 17:39:21 Re: [PATCH] pg_isready (was: [WIP] pg_ping utility)
Previous Message Tom Lane 2013-01-26 17:25:28 Re: proposal - assign result of query to psql variable