Обсуждение: \d output to a file

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

am  15.12.2004, um  6:38:22 -0800 mailte sarlav kumar folgendes:
> Hi All,
>
> I would like to write the output of the \d command on all tables in a database to an output file. There are more than
200tables in the database. I am aware of \o command to write the output to a file. But, it will be tough to do the \d
foreach table manually and write the output to a file. Is there a command/ way in which I can achieve this without
havingto do it for each table? 
> Any help in this regard would be really appreciated.

You can write a little shell-script to list all tables via \d and parse
the output to generate for each table a '\d table'.


Andreas
--
Andreas Kretschmer    (Kontakt: siehe Header)
               Tel. NL Heynitz:  035242/47212
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    ===

sarlav kumar wrote:
> Hi All,
>
> I would like to write the output of the \d command on all tables in a
> database to an output file. There are more than 200 tables in the
> database. I am aware of \o command to write the output to a file.
> But, it will be tough to do the \d for each table manually and write
> the output to a file. Is there a command/ way in which I can achieve
> this without having to do it for each table? Any help in this regard
> would be really appreciated.

What is the OS?  On any UNIX variant you can do:

echo '\d' | psql > outputfile

But this will get you the system tables as well I think.

Alternately you could do something like:

for table in $(<listoftables); do
    echo '\d' | psql
done > outputfile

--
Until later, Geoffrey

Andreas Kretschmer wrote:
> am  15.12.2004, um  6:38:22 -0800 mailte sarlav kumar folgendes:
>
>>Hi All,
>>
>>I would like to write the output of the \d command on all tables in a database to an output file. There are more than
200tables in the database. I am aware of \o command to write the output to a file. But, it will be tough to do the \d
foreach table manually and write the output to a file. Is there a command/ way in which I can achieve this without
havingto do it for each table? 
>>Any help in this regard would be really appreciated.
>
>
> You can write a little shell-script to list all tables via \d and parse
> the output to generate for each table a '\d table'.

Or:

for table in $(<filethatcontainsalistofthetables); do

    echo "\d $table" | psql $DATABASE > ${table}.out
done

--
Until later, Geoffrey

am  Wed, dem 15.12.2004, um 10:23:54 -0500 mailte Geoffrey folgendes:
> >>I would like to write the output of the \d command on all tables in a
> >>database to an output file. There are more than 200 tables in the
> >>database. I am aware of \o command to write the output to a file. But, it
> >>will be tough to do the \d for each table manually and write the output
> >>to a file. Is there a command/ way in which I can achieve this without
> >>having to do it for each table?
> >
> >You can write a little shell-script to list all tables via \d and parse
> >the output to generate for each table a '\d table'.
>
> Or:
>
> for table in $(<filethatcontainsalistofthetables); do

Yes, but you need the file called 'filethatcontainsalistofthetables' ;-)

echo "\d" | psql test_db | awk 'BEGIN{FS="|"}{if($3 ~ "Tabelle") {print "\d" $2}}' | psql test_db

It works, if the database named 'test_db' and if the output from \d in
the 3th row is 'Tabelle'.


Andreas
--
Diese Message wurde erstellt mit freundlicher Unterstützung eines freilau-
fenden Pinguins aus artgerechter Freilandhaltung.   Er ist garantiert frei
von Micro$oft'schen Viren. (#97922 http://counter.li.org)     GPG 7F4584DA
Was, Sie wissen nicht, wo Kaufbach ist? Hier: N 51.05082°, E 13.56889° ;-)

Geoffrey <esoteric@3times25.net> writes:
> sarlav kumar wrote:
>> I would like to write the output of the \d command on all tables in a
>> database to an output file.

> What is the OS?  On any UNIX variant you can do:
> echo '\d' | psql > outputfile

Or use \o:

regression=# \o zzz1
regression=# \d
regression=# \o
regression=# \d
             List of relations
 Schema |     Name      | Type  |  Owner
--------+---------------+-------+----------
 public | pg_ts_cfg     | table | postgres
 public | pg_ts_cfgmap  | table | postgres
 public | pg_ts_dict    | table | postgres
 public | pg_ts_parser  | table | postgres
 public | t_test        | table | postgres
 public | test_tsvector | table | postgres
(6 rows)

regression=# \q
$ cat zzz1
             List of relations
 Schema |     Name      | Type  |  Owner
--------+---------------+-------+----------
 public | pg_ts_cfg     | table | postgres
 public | pg_ts_cfgmap  | table | postgres
 public | pg_ts_dict    | table | postgres
 public | pg_ts_parser  | table | postgres
 public | t_test        | table | postgres
 public | test_tsvector | table | postgres
(6 rows)

$

            regards, tom lane

am  Wed, dem 15.12.2004, um 11:50:58 -0500 mailte Tom Lane folgendes:
> Geoffrey <esoteric@3times25.net> writes:
> > sarlav kumar wrote:
> >> I would like to write the output of the \d command on all tables in a
> >> database to an output file.

I remember: '\d command on all tables'


And you wrote:

>
> regression=# \q
> $ cat zzz1
>              List of relations
>  Schema |     Name      | Type  |  Owner
> --------+---------------+-------+----------
>  public | pg_ts_cfg     | table | postgres
>  public | pg_ts_cfgmap  | table | postgres

Sorry, but i think, this isn't the correct answer...


Andreas, leaning PostgreSQL and english...
--
Diese Message wurde erstellt mit freundlicher Unterstützung eines freilau-
fenden Pinguins aus artgerechter Freilandhaltung.   Er ist garantiert frei
von Micro$oft'schen Viren. (#97922 http://counter.li.org)     GPG 7F4584DA
Was, Sie wissen nicht, wo Kaufbach ist? Hier: N 51.05082°, E 13.56889° ;-)

On Wed, 15 Dec 2004 06:38:22 -0800 (PST), sarlav kumar
<sarlavk@yahoo.com> wrote:
> Hi All,
>
> I would like to write the output of the \d command on all tables in a
> database to an output file. There are more than 200 tables in the database.
> I am aware of \o command to write the output to a file. But, it will be
> tough to do the \d for each table manually and write the output to a file.
> Is there a command/ way in which I can achieve this without having to do it
> for each table?
> Any help in this regard would be really appreciated.
>
> Thanks,
> Saranya
>
>

Try something like:

psql -c "\d *" >listing.txt

On Wed, 2004-12-15 at 11:50 -0500, Tom Lane wrote:
> Geoffrey <esoteric@3times25.net> writes:
> > sarlav kumar wrote:
> >> I would like to write the output of the \d command on all tables in a
> >> database to an output file.
>
> > What is the OS?  On any UNIX variant you can do:
> > echo '\d' | psql > outputfile
>
> Or use \o:
>
> regression=# \o zzz1
> regression=# \d
or:
            =# \d *
to get all tables as th OP wanted

> regression=# \o

gnari

...and on Wed, Dec 15, 2004 at 06:38:22AM -0800, sarlav kumar used the keyboard:
> Hi All,
>
> I would like to write the output of the \d command on all tables in a database to an output file. There are more than
200tables in the database. I am aware of \o command to write the output to a file. But, it will be tough to do the \d
foreach table manually and write the output to a file. Is there a command/ way in which I can achieve this without
havingto do it for each table? 
> Any help in this regard would be really appreciated.
>

Hello Sarlav.

You don't say which platform you're doing this on. If it's Windows, someone
else will have to advise you; if it's a UNIX-like platform though, the
following simple shell script should be helpful in achieving what you want:

---CUT-HERE---
#!/bin/bash
if [ -z "$1" ]; then
    echo "Please specify a database to query."
    exit 1
fi
DATABASE=$1
MYTABLES="`echo '\t\a\dt' | psql -q ${DATABASE} | cut -f 2 -d '|'`"

for table in ${MYTABLES}; do
    echo '\d '${table}
done | psql ${DATABASE}
---CUT-HERE---

You can store this script into a file called, for example, describe.sh and
invoke it like so:

    $ ./describe.sh mydatabase > description.txt

It should then do what you want.

Should you have additional arguments to specify to psql, such as a host,
a username, a password and so on, it is easy to modify the script to do
that. Just supply those arguments in places where the "psql" command is
used.

Hope this helped,
--
    Grega Bremec
    gregab at p0f dot net