Horton

Thread

  1. VIP: new format for psql - shell - simple using psql in shell

    Pavel Stehule <pavel.stehule@gmail.com> — 2012-05-26T15:39:23Z

    Hello

I proposed new psql's format "shell". This format is optimized for
processing returned result in shell:


postgres=# select * from foo;
      a       | b  |     c
--------------+----+------------
 Hello, World | 10 | 2012-05-26
 Ahoj, Svete  | 20 | 2012-06-15
(2 rows)

postgres=# \pset format shell
Output format is shell.
postgres=# select * from foo;
a b c
Hello,\ World 10 2012-05-26
Ahoj,\ Svete 20 2012-06-15

postgres=# \x
Expanded display is on.
postgres=# select * from foo;
( c l )
( [a]=Hello,\ World [b]=10 [c]=2012-05-26 )
( [a]=Ahoj,\ Svete [b]=20 [c]=2012-06-15 )

shell scripts can looks like:

( psql -t -P format=shell postgres <<EOF
SELECT d.datname as "Name",
       pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
       pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
       d.datcollate as "Collate",
       d.datctype as "Ctype",
       pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;

EOF
) | while read dbname owner encoding collate ctype priv;
    do
      echo "DBNAME=$dbname OWNER=$owner PRIVILEGES=$priv";
    done;

or:

( psql -t -x -P format=shell postgres <<EOF
SELECT pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
       pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
       d.datcollate as "Collate",
       d.datctype as "Ctype",
       pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
   FROM pg_catalog.pg_database d
  ORDER BY 1;
EOF
) | (
while read r
    do
      declare -A row="$r"
      for field in "${!row[@]}"
      do
        echo  "$field -> ${row[$field]}"
      done;
      echo;
    done;)

I invite any comments, mainly from bash or shell experts

Regards

Pavel Stehule

  2. Re: VIP: new format for psql - shell - simple using psql in shell

    Bruce Momjian <bruce@momjian.us> — 2012-05-26T15:58:26Z

    On Sat, May 26, 2012 at 05:39:23PM +0200, Pavel Stehule wrote:
> Hello
> 
> I proposed new psql's format "shell". This format is optimized for
> processing returned result in shell:
> 
> 
> postgres=# select * from foo;
>       a       | b  |     c
> --------------+----+------------
>  Hello, World | 10 | 2012-05-26
>  Ahoj, Svete  | 20 | 2012-06-15
> (2 rows)
> 
> postgres=# \pset format shell
> Output format is shell.
> postgres=# select * from foo;
> a b c
> Hello,\ World 10 2012-05-26
> Ahoj,\ Svete 20 2012-06-15
> 
> postgres=# \x
> Expanded display is on.
> postgres=# select * from foo;
> ( c l )
> ( [a]=Hello,\ World [b]=10 [c]=2012-05-26 )
> ( [a]=Ahoj,\ Svete [b]=20 [c]=2012-06-15 )
...
> ) | while read dbname owner encoding collate ctype priv;

I am unclear exactly how this relates to shells.  Do shells read this
via read?  I am unclear that would actually work.  What do the brackets
mean?  Does read process \space as a non-space?

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

  3. Re: VIP: new format for psql - shell - simple using psql in shell

    Pavel Stehule <pavel.stehule@gmail.com> — 2012-05-26T16:42:07Z

    2012/5/26 Bruce Momjian <bruce@momjian.us>:
> On Sat, May 26, 2012 at 05:39:23PM +0200, Pavel Stehule wrote:
>> Hello
>>
>> I proposed new psql's format "shell". This format is optimized for
>> processing returned result in shell:
>>
>>
>> postgres=# select * from foo;
>>       a       | b  |     c
>> --------------+----+------------
>>  Hello, World | 10 | 2012-05-26
>>  Ahoj, Svete  | 20 | 2012-06-15
>> (2 rows)
>>
>> postgres=# \pset format shell
>> Output format is shell.
>> postgres=# select * from foo;
>> a b c
>> Hello,\ World 10 2012-05-26
>> Ahoj,\ Svete 20 2012-06-15
>>
>> postgres=# \x
>> Expanded display is on.
>> postgres=# select * from foo;
>> ( c l )
>> ( [a]=Hello,\ World [b]=10 [c]=2012-05-26 )
>> ( [a]=Ahoj,\ Svete [b]=20 [c]=2012-06-15 )
> ...
>> ) | while read dbname owner encoding collate ctype priv;
>
> I am unclear exactly how this relates to shells.  Do shells read this
> via read?  I am unclear that would actually work.  What do the brackets
> mean?  Does read process \space as a non-space?
>

"read" can read multicolumn files, where space is separator and real
space is escaped. It is first sample.

Second example is related to Bash's feature - associative array
support - data has format that is same like assoc array

Pavel



> --
>  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
>  EnterpriseDB                             http://enterprisedb.com
>
>  + It's impossible for everything to be true. +

  4. Re: VIP: new format for psql - shell - simple using psql in shell

    Tom Lane <tgl@sss.pgh.pa.us> — 2012-05-26T16:43:40Z

    Bruce Momjian <bruce@momjian.us> writes:
> On Sat, May 26, 2012 at 05:39:23PM +0200, Pavel Stehule wrote:
>> I proposed new psql's format "shell". This format is optimized for
>> processing returned result in shell:

> I am unclear exactly how this relates to shells.

What I'm unclear on is why we'd want to encourage that style of
programming.  The most charitable prediction of performance is that it
would suck --- not only do you have all the inefficiencies inherent in
row-by-row result processing with a shell script, but you're forcing a
separate database connection for each query.  And I don't actually see
where it would be especially convenient to use, compared to say perl
or python or other scripting languages.  I'd rather see us worrying
about the convenience of cases like

	psql ... | perl -e ...

			regards, tom lane

  5. Re: VIP: new format for psql - shell - simple using psql in shell

    Pavel Stehule <pavel.stehule@gmail.com> — 2012-05-26T16:50:17Z

    2012/5/26 Tom Lane <tgl@sss.pgh.pa.us>:
> Bruce Momjian <bruce@momjian.us> writes:
>> On Sat, May 26, 2012 at 05:39:23PM +0200, Pavel Stehule wrote:
>>> I proposed new psql's format "shell". This format is optimized for
>>> processing returned result in shell:
>
>> I am unclear exactly how this relates to shells.
>
> What I'm unclear on is why we'd want to encourage that style of
> programming.  The most charitable prediction of performance is that it
> would suck --- not only do you have all the inefficiencies inherent in
> row-by-row result processing with a shell script, but you're forcing a
> separate database connection for each query.  And I don't actually see
> where it would be especially convenient to use, compared to say perl
> or python or other scripting languages.  I'd rather see us worrying
> about the convenience of cases like
>
>        psql ... | perl -e ...

A performance is not important in this case - typical use case for
this feature are simple tasks - some simple maintaining - where people
can prepare SQL in psql, and later can reuse knowledge in some simple
scripts. Shell has one significant advantage against perl or python -
is everywhere (on UNIX) and it is best for very simple tasks.

Regards

Pavel



>
>                        regards, tom lane

  6. Re: VIP: new format for psql - shell - simple using psql in shell

    Bruce Momjian <bruce@momjian.us> — 2012-05-26T17:24:47Z

    On Sat, May 26, 2012 at 12:43:40PM -0400, Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > On Sat, May 26, 2012 at 05:39:23PM +0200, Pavel Stehule wrote:
> >> I proposed new psql's format "shell". This format is optimized for
> >> processing returned result in shell:
> 
> > I am unclear exactly how this relates to shells.
> 
> What I'm unclear on is why we'd want to encourage that style of
> programming.  The most charitable prediction of performance is that it
> would suck --- not only do you have all the inefficiencies inherent in
> row-by-row result processing with a shell script, but you're forcing a
> separate database connection for each query.  And I don't actually see
> where it would be especially convenient to use, compared to say perl
> or python or other scripting languages.  I'd rather see us worrying
> about the convenience of cases like

Wouldn't you just us unaligned mode for this, and set IFS  to '|'?

	$ psql --no-align --tuples-only -c 'SELECT 1,2' test
	1|2

	$ export IFS="|"
	$ IFS='|' sql --no-align --tuples-only -c 'SELECT 1,2' test | 
	while read x y; do echo $x; echo $y; done
	1
	2

Are you worried about pipes in data?  Does you idea fix this?

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

  7. Re: VIP: new format for psql - shell - simple using psql in shell

    Jan-Benedict Glaw <jbglaw@lug-owl.de> — 2012-05-26T18:57:41Z

    Hi!

On Sat, 2012-05-26 17:39:23 +0200, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> postgres=# select * from foo;
[...]
> postgres=# \pset format shell
> Output format is shell.
> postgres=# select * from foo;
> a b c
> Hello,\ World 10 2012-05-26
> Ahoj,\ Svete 20 2012-06-15
[...]

I like that idea!  Up to now, I basically used IFS='|' with
tuples-only, but it's not clean wrt. '\n' within the data.

I didn't check your patch if it gets this "right", but if it does, it
would ease daily work (where I check for '\n' in the data beforehand
and abort...)

MfG, JBG

-- 
      Jan-Benedict Glaw      jbglaw@lug-owl.de              +49-172-7608481
Signature of:              Fortschritt bedeutet, einen Schritt so zu machen,
the second  :                   daß man den nächsten auch noch machen kann.

  8. Re: VIP: new format for psql - shell - simple using psql in shell

    Pavel Stehule <pavel.stehule@gmail.com> — 2012-05-27T03:16:20Z

    2012/5/26 Bruce Momjian <bruce@momjian.us>:
> On Sat, May 26, 2012 at 12:43:40PM -0400, Tom Lane wrote:
>> Bruce Momjian <bruce@momjian.us> writes:
>> > On Sat, May 26, 2012 at 05:39:23PM +0200, Pavel Stehule wrote:
>> >> I proposed new psql's format "shell". This format is optimized for
>> >> processing returned result in shell:
>>
>> > I am unclear exactly how this relates to shells.
>>
>> What I'm unclear on is why we'd want to encourage that style of
>> programming.  The most charitable prediction of performance is that it
>> would suck --- not only do you have all the inefficiencies inherent in
>> row-by-row result processing with a shell script, but you're forcing a
>> separate database connection for each query.  And I don't actually see
>> where it would be especially convenient to use, compared to say perl
>> or python or other scripting languages.  I'd rather see us worrying
>> about the convenience of cases like
>
> Wouldn't you just us unaligned mode for this, and set IFS  to '|'?
>
>        $ psql --no-align --tuples-only -c 'SELECT 1,2' test
>        1|2
>
>        $ export IFS="|"
>        $ IFS='|' sql --no-align --tuples-only -c 'SELECT 1,2' test |
>        while read x y; do echo $x; echo $y; done
>        1
>        2
>
> Are you worried about pipes in data?  Does you idea fix this?

I can do use IFS, but it is not easy when you would to work with
multicolumn tables - because you have to two IFS. Processing single
column tables is simple now - difference is in multicolumn tables.

My idea is secure to separator - because separator is just space and
new line and these symbols are escaped.

Regards

Pavel

>
> --
>  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
>  EnterpriseDB                             http://enterprisedb.com
>
>  + It's impossible for everything to be true. +

  9. Re: VIP: new format for psql - shell - simple using psql in shell

    Pavel Stehule <pavel.stehule@gmail.com> — 2012-05-27T03:18:32Z

    2012/5/26 Jan-Benedict Glaw <jbglaw@lug-owl.de>:
> Hi!
>
> On Sat, 2012-05-26 17:39:23 +0200, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>> postgres=# select * from foo;
> [...]
>> postgres=# \pset format shell
>> Output format is shell.
>> postgres=# select * from foo;
>> a b c
>> Hello,\ World 10 2012-05-26
>> Ahoj,\ Svete 20 2012-06-15
> [...]
>
> I like that idea!  Up to now, I basically used IFS='|' with
> tuples-only, but it's not clean wrt. '\n' within the data.
>
> I didn't check your patch if it gets this "right", but if it does, it
> would ease daily work (where I check for '\n' in the data beforehand
> and abort...)
>

please, test it. I am long time bash user, but my knowledge is not too
strong, and any second ayes are welcome.

Regards

Pavel

> MfG, JBG
>
> --
>      Jan-Benedict Glaw      jbglaw@lug-owl.de              +49-172-7608481
> Signature of:              Fortschritt bedeutet, einen Schritt so zu machen,
> the second  :                   daß man den nächsten auch noch machen kann.
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.10 (GNU/Linux)
>
> iEYEARECAAYFAk/BJ6UACgkQHb1edYOZ4btypgCeKC4I2MwzPYPbTwjmFxAnzQPt
> +ykAn3B6oNnutk80Ige31qxjzsXrTRid
> =CJvM
> -----END PGP SIGNATURE-----
>

  10. Re: VIP: new format for psql - shell - simple using psql in shell

    Abel Abraham Camarillo Ojeda <acamari@verlet.org> — 2012-05-27T03:38:20Z

    On Sat, May 26, 2012 at 11:50 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> 2012/5/26 Tom Lane <tgl@sss.pgh.pa.us>:
>> Bruce Momjian <bruce@momjian.us> writes:
>>> On Sat, May 26, 2012 at 05:39:23PM +0200, Pavel Stehule wrote:
>>>> I proposed new psql's format "shell". This format is optimized for
>>>> processing returned result in shell:
>>
>>> I am unclear exactly how this relates to shells.
>>
>> What I'm unclear on is why we'd want to encourage that style of
>> programming.  The most charitable prediction of performance is that it
>> would suck --- not only do you have all the inefficiencies inherent in
>> row-by-row result processing with a shell script, but you're forcing a
>> separate database connection for each query.  And I don't actually see
>> where it would be especially convenient to use, compared to say perl
>> or python or other scripting languages.  I'd rather see us worrying
>> about the convenience of cases like
>>
>>        psql ... | perl -e ...
>
> A performance is not important in this case - typical use case for
> this feature are simple tasks - some simple maintaining - where people
> can prepare SQL in psql, and later can reuse knowledge in some simple
> scripts. Shell has one significant advantage against perl or python -
> is everywhere (on UNIX) and it is best for very simple tasks.
>
> Regards
>
> Pavel
>
>
>
>>
>>                        regards, tom lane
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

bash isn't everywhere (on UNIX)...

  11. Re: VIP: new format for psql - shell - simple using psql in shell

    Pavel Stehule <pavel.stehule@gmail.com> — 2012-05-27T03:40:53Z

    >
> bash isn't everywhere (on UNIX)...

it is true - but first format - space is used as separator and space
is escaped should be processed on every shell.

Regards

Pavel

  12. Re: VIP: new format for psql - shell - simple using psql in shell

    hubert depesz lubaczewski <depesz@depesz.com> — 2012-05-27T12:15:20Z

    On Sat, May 26, 2012 at 05:39:23PM +0200, Pavel Stehule wrote:
> I proposed new psql's format "shell". This format is optimized for
> processing returned result in shell:

While I generally like the idea, please note that safe reading output
from queries is possible, with COPY, and proper IFS, like:

=$ psql -c "select * from t"
 a  |  b  |     c     
----+-----+-----------
 a1 | b 2 | c|3
 a +| b  +| c:|     6
 4  | 5  +| 
    |     | 
(2 rows)


=$ psql -qAtX -c "copy (select * from t) to stdout" | while IFS=$'\t' read -r a b c; do echo -e "a=[$a] b=[$b] c=[$c]"; done
a=[a1] b=[b 2] c=[c|3]
a=[a
4] b=[b
5
] c=[c:|        6]

that being said - I would love to get more functional psql.

Best regards,

depesz

-- 
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/

  13. Re: VIP: new format for psql - shell - simple using psql in shell

    Pavel Stehule <pavel.stehule@gmail.com> — 2012-05-27T13:16:28Z

    Hello

2012/5/27 hubert depesz lubaczewski <depesz@depesz.com>:
> On Sat, May 26, 2012 at 05:39:23PM +0200, Pavel Stehule wrote:
>> I proposed new psql's format "shell". This format is optimized for
>> processing returned result in shell:
>
> While I generally like the idea, please note that safe reading output
> from queries is possible, with COPY, and proper IFS, like:

I newer say so it is impossible

>
> =$ psql -c "select * from t"
>  a  |  b  |     c
> ----+-----+-----------
>  a1 | b 2 | c|3
>  a +| b  +| c:|     6
>  4  | 5  +|
>    |     |
> (2 rows)
>
>
> =$ psql -qAtX -c "copy (select * from t) to stdout" | while IFS=$'\t' read -r a b c; do echo -e "a=[$a] b=[$b] c=[$c]"; done
> a=[a1] b=[b 2] c=[c|3]
> a=[a
> 4] b=[b
> 5
> ] c=[c:|        6]
>

I know about this feature

http://archives.postgresql.org/pgsql-hackers/2012-05/msg01169.php

but may "shell format" patch is very simple and can really simplify
usage in shell.

> that being said - I would love to get more functional psql.

This patch doesn't break anything - and it is only 30 lines of non
invasive simple code.

Implementation of statements to psql is probably long task - I wrote
prototype - but I have not time finish it and push to core.

Regards

Pavel
>
> Best regards,
>
> depesz
>
> --
> The best thing about modern society is how easy it is to avoid contact with it.
>                                                             http://depesz.com/

  14. Re: VIP: new format for psql - shell - simple using psql in shell

    Jim Nasby <jim@nasby.net> — 2012-06-04T20:32:33Z

    On 5/26/12 10:16 PM, Pavel Stehule wrote:
> My idea is secure to separator - because separator is just space and
> new line and these symbols are escaped.

ISTM it'd be a really good idea to support something other than space, since presumably that'd be trivial.

I'm not a fan of supporting the array construction. If you get to that level of complexity in bash it's a really good sign that you need to use a real language.

If we do add array support then it needs to be keyed to the actual shell in use, because it's inconsistent between them.
-- 
Jim C. Nasby, Database Architect                   jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net