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