pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to query information schema from shell script
11+ messages / 6 participants
[nested] [flat]
* How to query information schema from shell script
@ 2006-10-27 13:31 Jon Horsman <horshaq@gmail.com>
0 siblings, 3 replies; 11+ messages in thread
From: Jon Horsman @ 2006-10-27 13:31 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How to query information schema from shell script
@ 2006-10-27 13:46 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Jon Horsman <horshaq@gmail.com>
2 siblings, 0 replies; 11+ messages in thread
From: A. Kretschmer @ 2006-10-27 13:46 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How to query information schema from shell script
@ 2006-10-27 13:47 George Pavlov <gpavlov@mynewplace.com>
parent: Jon Horsman <horshaq@gmail.com>
2 siblings, 0 replies; 11+ messages in thread
From: George Pavlov @ 2006-10-27 13:47 UTC (permalink / raw)
To: Jon Horsman <horshaq@gmail.com>; pgsql-sql
> 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How to query information schema from shell script
@ 2006-10-27 13:55 William Leite Araújo <william.bh@gmail.com>
parent: Jon Horsman <horshaq@gmail.com>
2 siblings, 1 reply; 11+ messages in thread
From: William Leite Araújo @ 2006-10-27 13:55 UTC (permalink / raw)
To: Jon Horsman <horshaq@gmail.com>; +Cc: pgsql-sql
You can use "-c" option of psql client.
Ex.: psql -p 5000 -d database -c "SELECT * FROM
information_schema.tables WHERE table_schema = 'public'"
2006/10/27, Jon Horsman <horshaq@gmail.com>:
>
> 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.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
--
William Leite Araújo
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How to query information schema from shell script
@ 2006-10-27 13:57 Jon Horsman <horshaq@gmail.com>
parent: William Leite Araújo <william.bh@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Jon Horsman @ 2006-10-27 13:57 UTC (permalink / raw)
To: pgsql-sql
Thanks for the suggestions guys, i'll give this a try.
Jon
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How to query information schema from shell script
@ 2006-10-27 15:12 Jon Horsman <horshaq@gmail.com>
parent: Jon Horsman <horshaq@gmail.com>
0 siblings, 4 replies; 11+ messages in thread
From: Jon Horsman @ 2006-10-27 15:12 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How to query information schema from shell script
@ 2006-10-27 15:22 Jon Horsman <horshaq@gmail.com>
parent: Jon Horsman <horshaq@gmail.com>
3 siblings, 0 replies; 11+ messages in thread
From: Jon Horsman @ 2006-10-27 15:22 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How to query information schema from shell script
@ 2006-10-27 15:43 George Pavlov <gpavlov@mynewplace.com>
parent: Jon Horsman <horshaq@gmail.com>
3 siblings, 0 replies; 11+ messages in thread
From: George Pavlov @ 2006-10-27 15:43 UTC (permalink / raw)
To: Jon Horsman <horshaq@gmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How to query information schema from shell script
@ 2006-10-27 19:14 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Jon Horsman <horshaq@gmail.com>
3 siblings, 1 reply; 11+ messages in thread
From: Scott Marlowe @ 2006-10-27 19:14 UTC (permalink / raw)
To: Jon Horsman <horshaq@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How to query information schema from shell script
@ 2006-10-28 04:09 Richard Broersma Jr <rabroersma@yahoo.com>
parent: Jon Horsman <horshaq@gmail.com>
3 siblings, 0 replies; 11+ messages in thread
From: Richard Broersma Jr @ 2006-10-28 04:09 UTC (permalink / raw)
To: Jon Horsman <horshaq@gmail.com>; pgsql-sql
> 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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How to query information schema from shell script
@ 2006-10-28 15:18 Jon Horsman <horshaq@gmail.com>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 0 replies; 11+ messages in thread
From: Jon Horsman @ 2006-10-28 15:18 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2006-10-28 15:18 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-10-27 13:31 How to query information schema from shell script Jon Horsman <horshaq@gmail.com>
2006-10-27 13:46 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2006-10-27 13:47 ` George Pavlov <gpavlov@mynewplace.com>
2006-10-27 13:55 ` William Leite Araújo <william.bh@gmail.com>
2006-10-27 13:57 ` Jon Horsman <horshaq@gmail.com>
2006-10-27 15:12 ` Jon Horsman <horshaq@gmail.com>
2006-10-27 15:22 ` Jon Horsman <horshaq@gmail.com>
2006-10-27 15:43 ` George Pavlov <gpavlov@mynewplace.com>
2006-10-27 19:14 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-10-28 15:18 ` Jon Horsman <horshaq@gmail.com>
2006-10-28 04:09 ` Richard Broersma Jr <rabroersma@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox