Thread: 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
Attachment
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. +
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. +
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 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
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' test1|2 $ export IFS="|"$ IFS='|' sql --no-align --tuples-only -c 'SELECT 1,2' test | while read x y; do echo $x; echo $y; done12 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 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. +
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----- >
Re: VIP: new format for psql - shell - simple using psql in shell
From
Abel Abraham Camarillo Ojeda
Date:
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)...
> > 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
Re: VIP: new format for psql - shell - simple using psql in shell
From
hubert depesz lubaczewski
Date:
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|3a +| b +| c:| 64 | 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 > > 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 signthat 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