better integration psql with bash
I wrote very simple patch for psql, that enable shell format. Enabling this format ensures, so psql output will be optimized for processing in bash - and you can write scripts 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 with bash associative arrays:( 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
) | (declare -A row
while read r
do
declare -A row="$r"
for field in "${!row[@]}"
do
echo "$field -> ${row[$field]}"
done;
echo;
done;)
the output of query and "shell" format looks like:Name Owner Encoding Collate Ctype Access\ privileges jqerqwer,\ werwer pavel UTF8 cs_CZ.utf-8 cs_CZ.utf-8 \[null\] postgres postgres UTF8 cs_CZ.utf-8 cs_CZ.utf-8 \[null\] some\ stupid\ name pavel UTF8 cs_CZ.utf-8 cs_CZ.utf-8 \[null\] template0 postgres UTF8 cs_CZ.utf-8 cs_CZ.utf-8 =c/postgres\\npostgres=CTc/postgres template1 postgres UTF8 cs_CZ.utf-8 cs_CZ.utf-8 =c/postgres\\npostgres=CTc/postgresor (for assoc. arrays)
( c l ) ( [Name]=jqerqwer,\ werwer [Owner]=pavel [Encoding]=UTF8 [Collate]=cs_CZ.utf-8 [Ctype]=cs_CZ.utf-8 [Access\ privileges]=\[null\] ) ( [Name]=postgres [Owner]=postgres [Encoding]=UTF8 [Collate]=cs_CZ.utf-8 [Ctype]=cs_CZ.utf-8 [Access\ privileges]=\[null\] ) ( [Name]=some\ stupid\ name [Owner]=pavel [Encoding]=UTF8 [Collate]=cs_CZ.utf-8 [Ctype]=cs_CZ.utf-8 [Access\ privileges]=\[null\] ) ( [Name]=template0 [Owner]=postgres [Encoding]=UTF8 [Collate]=cs_CZ.utf-8 [Ctype]=cs_CZ.utf-8 [Access\ privileges]==c/postgres\\npostgres=CTc/postgres ) ( [Name]=template1 [Owner]=postgres [Encoding]=UTF8 [Collate]=cs_CZ.utf-8 [Ctype]=cs_CZ.utf-8 [Access\ privileges]==c/postgres\\npostgres=CTc/postgres )
I invite any ideas and notes

