Thread: How to query information schema from shell script

Hey,

I'm trying to figure out how i can query the postgres information
schema from a bourne shell script on linux.  I need to know if a
user/table exists.  Does someone mind giving me a quick example of how
this works, is this possible?

Thanks,

Jon.

> I'm trying to figure out how i can query the postgres information
> schema from a bourne shell script on linux.  I need to know if a
> user/table exists.  Does someone mind giving me a quick example of how
> this works, is this possible?

% for tn in `psql -Umnp -dmnp_gp -hstgdb0 -tA -c"select table_name from
information_schema.tables where table_name like 'employee%'"`; do
for> echo FOUND TABLE $tn
for> done
FOUND TABLE employee_role
FOUND TABLE employee

hope this helps,

george

am  Fri, dem 27.10.2006, um  9:31:03 -0400 mailte Jon Horsman folgendes:
> Hey,
> 
> I'm trying to figure out how i can query the postgres information
> schema from a bourne shell script on linux.  I need to know if a
> user/table exists.  Does someone mind giving me a quick example of how

You can do something like this:

bla=`echo "select bla from fasel" | psql -t`

You have then the result in $bla.


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47215,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

     You can use "-c" option of psql client.
     Ex.: psql -p 5000 -d database -c "SELECT * FROM information_schema.tables WHERE table_schema = 'public'"

   




--
William Leite Araújo

Thanks for the suggestions guys, i'll give this a try.

Jon

In my original email i forgot to mentioned i need to know if the
database exists or not from the shell script.  If it doesn't exist i
would then create it.  Currently i was just creating the db everytime
our db script is run (since it doesn't hurt the db) but this generates
the 'db already exists' log and I now have the requirement that the
users not see that log.  ie, don't run createdb if it already exists.

In looking through the postgres docs i can see how to check if a table
exists but not how a db exists.

Again, thanks for the help.

Jon

For more information, i know how this is done using the pg_catalog but
have yet to find out how its done with the information schema.

select * from pg_catalog.pg_database where datname='test'

will work for me but i can't find a solution with the IS.  I'd prefer
to use the IS if its as easily done as with pg_catalog

Jon

simplest might be psql -l.

> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Jon Horsman
> Sent: Friday, October 27, 2006 8:13 AM
> To: pgsql-sql@postgresql.org
> Subject: Re: [SQL] How to query information schema from shell script
>
> In my original email i forgot to mentioned i need to know if the
> database exists or not from the shell script.  If it doesn't exist i
> would then create it.  Currently i was just creating the db everytime
> our db script is run (since it doesn't hurt the db) but this generates
> the 'db already exists' log and I now have the requirement that the
> users not see that log.  ie, don't run createdb if it already exists.
>
> In looking through the postgres docs i can see how to check if a table
> exists but not how a db exists.
>
> Again, thanks for the help.
>
> Jon

On Fri, 2006-10-27 at 10:12, Jon Horsman wrote:
> In my original email i forgot to mentioned i need to know if the
> database exists or not from the shell script.  If it doesn't exist i
> would then create it.  Currently i was just creating the db everytime
> our db script is run (since it doesn't hurt the db) but this generates
> the 'db already exists' log and I now have the requirement that the
> users not see that log.  ie, don't run createdb if it already exists.
> 
> In looking through the postgres docs i can see how to check if a table
> exists but not how a db exists.
> 
> Again, thanks for the help.

Oh, and read this:

http://www.gnu.org/software/bash/manual/bashref.html

It's a good starters manual for bash / shell programming.

For more advanced tutorials, look at:
www.deadman.org/bash.html
www.tldp.org/LDP/abc/html/index.html

> In my original email i forgot to mentioned i need to know if the
> database exists or not from the shell script.  If it doesn't exist i
> would then create it.  Currently i was just creating the db everytime
> our db script is run (since it doesn't hurt the db) but this generates
> the 'db already exists' log and I now have the requirement that the
> users not see that log.  ie, don't run createdb if it already exists.
> 
> In looking through the postgres docs i can see how to check if a table
> exists but not how a db exists.
> 
> Again, thanks for the help.

psql -l

Regards,

Richard Broersma Jr.

I ended up going the pg_catalog route, something like (off the top of
my head without a linux machine by to test it)

output=$(psql -d template1 -t -c "select * from pg_database where
datname='testdb'")
if [ -z "$output" ]; then
psql createdb testdb
else
echo 'the db already exists'
fi

I did the exact same thing for the user using pg_user and usename

Jon