VIP: new format for psql - shell - simple using psql in shell
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
Attachments:
format_shell.diffapplication/octet-stream; name=format_shell.diffDownload+153-3
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-15postgres=# \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. +
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-15postgres=# \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
Show quoted text
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com+ It's impossible for everything to be true. +
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
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 likepsql ... | 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
Show quoted text
regards, tom lane
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. +
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.
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 likeWouldn'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
2Are 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
Show quoted text
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com+ It's impossible for everything to be true. +
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
Show quoted text
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-----
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 likepsql ... | 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)...
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
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/
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
Show quoted text
Best regards,
depesz
--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/
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