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

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: VIP: new format for psql - shell - simple using psql in shell
Date: 2012-05-26 15:39:23
Message-ID: CAFj8pRCO4SxxcWXSWxJMdK4eriA6yu2VC2f1syMJnxtK2v23Aw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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[(at)]}"
do
echo "$field -> ${row[$field]}"
done;
echo;
done;)

I invite any comments, mainly from bash or shell experts

Regards

Pavel Stehule

Attachment Content-Type Size
format_shell.diff application/octet-stream 5.8 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Euler Taveira 2012-05-26 15:54:25 Re: No, pg_size_pretty(numeric) was not such a hot idea
Previous Message Greg Sabino Mullane 2012-05-26 15:17:14 Re: Backends stalled in 'startup' state: index corruption