Re: scripting & psql issues

Dino Vliet wrote:
> Hi folks,
> probably this is a question you've heard so many times
> but I wasn't able to find a solution to it.
>
> I'm using a shell script to create a textfile for me.
> It looks like
> #!/usr/local/bin/bash
> psql -c "select foo from bar;" -d database1 -t
> psql -c "\q" -d database1
> exit 0

Put your commands in a separate file and run them with psql -f myfile.
That's simplest.

> The second psql -c "\q" command is used to logout from
> the first database. But I'm not sure whether this is
> correct. How do I mix these sql and psql meta-commands
> otherwise if I want to be able to use myscript >
> output.txt to get the output from this select
> statement in the file output.txt. I'm planning to
> write a cron job for it too.

You can set an output file from psql with \o output.txt.

> Another issues is passwords. Normally when I log onto
> database1, I have to issue a password because only the
> superuser is trusted in the pg_hba.conf file. However
> for scripts that I want to be able to run in the
> background like this one, I had to add an entry to the
> pg_hba.conf file to trust this user also. But as from
> security point of view, I'm wondering if this is the
> best way to achieve it.

You'll want to investigate the .pgpass file. See the chapter:
Client Interfaces / libpq / files

--
Richard Huxton
Archonet Ltd