Saturday, May 26, 2012

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/postgres
or (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