Обсуждение: psql UPDATE field [tab] expands to DEFAULT?

On 6/17/19 3:03 PM, Ken Tanzer wrote:
> Hi.  If I'm using psql, and type for example:
> 
> UPDATE my_table SET my_field
> (with a trailing space)
> 
> and then hit Tab, it will expand that to an =, and then another tab will 
> expand to DEFAULT, so that I then have:
> 
> UPDATE my_table SET my_field = DEFAULT
> 
> If I'm tabbing out in this situation, it's going to be after the =, and 
> I will have typed "myreal"[tab] in the vain hope that psql will complete 
> that to "myreallylongfieldname," but instead it gets replaced with DEFAULT.
> 
> So I'm curious if this is intended behavior, if it's considered useful, 
> and/or if it's a placeholder for something in the future that will be 
> useful.  Also, is this new, as I've never noticed it before?

Not sure how long that has been around.

My cheat for dealing with many/long column names is:

test=# \d up_test
               Table "public.up_test"
  Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
  id     | integer |           |          |
  col1   | boolean |           |          |
  col2   | integer |           |          |



test=# \pset format unaligned
Output format is unaligned.
test=# \pset fieldsep ','
Field separator is ",".

select * from up_test limit 0;
id,col1,col2

Cut and paste above.

test=# \pset fieldsep '|'
Field separator is "|".

test=# \pset format 'aligned'
Output format is aligned.


> 
> Thanks in advance,
> Ken
> 
> p.s.,  Version 9.6.13
> 
> -- 
> AGENCY Software
> A Free Software data system
> By and for non-profits
> /http://agency-software.org//
> /https://demo.agency-software.org/client/
> ken.tanzer@agency-software.org <mailto:ken.tanzer@agency-software.org>
> (253) 245-3801
> 
> Subscribe to the mailing list 
> <mailto:agency-general-request@lists.sourceforge.net?body=subscribe> to
> learn more about AGENCY or
> follow the discussion.


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 6/17/19 4:33 PM, Ken Tanzer wrote:
> On Mon, Jun 17, 2019 at 4:24 PM Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 6/17/19 3:03 PM, Ken Tanzer wrote:
>      >
>      > So I'm curious if this is intended behavior, if it's considered
>     useful,
>      > and/or if it's a placeholder for something in the future that
>     will be
>      > useful.  Also, is this new, as I've never noticed it before?
> 
>     Not sure how long that has been around.
> 
>     My cheat for dealing with many/long column names is:
> 
> 
> Thanks Adrian, though I wasn't really seeking tips for column names.  I 
> was instead trying to understand whether this particular tab expansion 
> was intentional and considered useful, and if so what that usefulness 

If I am following the below correctly it is intentional:


https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/bin/psql/tab-complete.c;h=68a2ba27aec22302625c5481a8f74cf866f4dc23;hb=d22ca701a39dfd03cdfa1ca238370d34f4bc4ac4

Line 2888

Useful, that is in the eye of the beholder:)

> was, because it's rather escaping me!
> 
> Cheers,
> Ken
> 
> 



-- 
Adrian Klaver
adrian.klaver@aklaver.com

Tim Cross <theophilusx@gmail.com> writes:
> On Tue, 18 Jun 2019 at 09:34, Ken Tanzer <ken.tanzer@gmail.com> wrote:
>> Thanks Adrian, though I wasn't really seeking tips for column names.  I
>> was instead trying to understand whether this particular tab expansion was
>> intentional and considered useful, and if so what that usefulness was,
>> because it's rather escaping me!

> Have to say, I fid that behaviour unusual as well.

I don't think it's intentional.  A look into tab-complete.c shows that it
makes no attempt to offer completions beyond the "=" part of the syntax;
so there's room for improvement there.  But then what is producing the
"DEFAULT" completion?  After looking around a bit, I think it's
accidentally matching the pattern for a GUC "set" command:

    else if (TailMatches("SET", MatchAny, "TO|="))
    {
        /* special cased code for individual GUCs */
        ...
        else
            COMPLETE_WITH("DEFAULT");
    }

So perhaps that needs to look more like this other place where somebody
already noticed the conflict against UPDATE:

    else if (TailMatches("SET|RESET") && !TailMatches("UPDATE", MatchAny, "SET"))
        COMPLETE_WITH_QUERY(Query_for_list_of_set_vars);

More generally, though, I'm inclined to think that offering DEFAULT
and nothing else, which is what this code does if it doesn't recognize
the "GUC name", is just ridiculous.  If the word after SET is not a known
GUC name then we probably have misconstrued the context, as indeed is
happening in your example; and in any case DEFAULT is about the least
likely thing for somebody to be trying to enter here.  (They'd probably
have selected RESET not SET if they were trying to do that.)

            regards, tom lane

On 6/18/19 3:23 PM, Ken Tanzer wrote:
> On Mon, Jun 17, 2019 at 4:24 PM Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
> 
>     My cheat for dealing with many/long column names is:
> 
>     test=# \d up_test
>                     Table "public.up_test"
>        Column |  Type   | Collation | Nullable | Default
>     --------+---------+-----------+----------+---------
>        id     | integer |           |          |
>        col1   | boolean |           |          |
>        col2   | integer |           |          |
> 
> 
> 
>     test=# \pset format unaligned
>     Output format is unaligned.
>     test=# \pset fieldsep ','
>     Field separator is ",".
> 
>     select * from up_test limit 0;
>     id,col1,col2
> 
>     Cut and paste above.
> 
>     test=# \pset fieldsep '|'
>     Field separator is "|".
> 
>     test=# \pset format 'aligned'
>     Output format is aligned.
> 
> 
> Just curious, but if you really do that often, wouldn't you be better 
> off with something like this?

I could/should I just don't do the above enough to get motivated to 
build a function. Most cases where I'm doing complicated updates I am 
not using psql I am building then in Python from a dict.

> 
> CREATE OR REPLACE FUNCTION field_list( name ) RETURNS text AS $$
> 
> SELECT array_to_string(array_agg(column_name::text ORDER BY 
> ordinal_position),',') FROM information_schema.columns WHERE table_name 
> = $1;
> 
> $$ LANGUAGE sql STABLE;
> 
> Cheers,
> Ken
> 



-- 
Adrian Klaver
adrian.klaver@aklaver.com