Re: How to query pgsql from a BASH script ?

On Sun, 3 Apr 2005, Michelle Konzack wrote:

> Hello *,
>
> I am puzzeling around, how to query a postgresql from a BASH script.
> Generaly it must do nothing else as
>

Hello,

There is more solutions. The best is pgbash (pgbash is patch for bash)
http://www.psn.co.jp/PostgreSQL/pgbash/index-e.html

and last two months there somebody released new functions for bash and
postgresql but I can't remember

/usr/local/bin/pgbash
connect to testdb011;
BEGIN;
DECLARE c CURSOR FOR
  SELECT name FROM names WHERE name LIKE '$1%';
lines=1
FETCH IN c INTO :name;
while [ $SQLCODE -eq $SQL_OK ]; do
  if [ $lines -gt $2 ] ; then
    break
  fi
  echo $name
  let "lines+=1"
  FETCH IN c INTO :name;
done
END;
disconnect all;

or

#!/usr/local/bin/pgbash
connect to template1;

set option_header=off;
set option_bottom=off;
set option_alignment=off;
set option_separator=;

dblist=`SELECT d.datname FROM
    pg_catalog.pg_database d LEFT JOIN pg_catalog.pg_user u
    ON d.datdba = u.usesysid WHERE u.usename LIKE '$1';`

if [ "$dblist" != "" ]; then
  echo "$dblist" | while read db; do
    echo "Remove database $db"
    DROP DATABASE \"$db\";
  done
fi
disconnect all;

regards
Pavel Stehule