Thread: New to list, quick question.
Hello everyone. I have been using PostgreSQL for about 3-4 years now, it's wonderful how I can make it do things and write Perl programs to interface with it. Since I do all of the DBA behind the scenes on our company Intranet (Linux-Apache based), and I really prefer writing Perl programs to do my simple admin functions. I really like those \d commands (via telnet) and they tell me a lot of what I need for writing new programs etc, but I would really like a way to use those through my Perl programs, but haven't found a way to do so, has anyone done this in this manner yet? Also, I'd like a way to query a table to find out how much space it is using, like it's percentage for example, is there a way to do that? Excuse me if these questions have been asked before, I tried to search the LIST area and I also did not see anything related to this in the FAQS. Thanks for any info you can provide me. -- Rich Parker http://www.fssi-ca.com mailto:RichParker@fssi-ca.com
@results = `print \'\\d\' |/usr/local/pgsql/bin/psql travis`; Travis -----Original Message----- From: Rich Parker [mailto:RichParker@fssi-ca.com] Sent: Friday, August 15, 2003 10:35 AM To: pgsql-general@postgresql.org Subject: [GENERAL] New to list, quick question. Hello everyone. I have been using PostgreSQL for about 3-4 years now, it's wonderful how I can make it do things and write Perl programs to interface with it. Since I do all of the DBA behind the scenes on our company Intranet (Linux-Apache based), and I really prefer writing Perl programs to do my simple admin functions. I really like those \d commands (via telnet) and they tell me a lot of what I need for writing new programs etc, but I would really like a way to use those through my Perl programs, but haven't found a way to do so, has anyone done this in this manner yet? Also, I'd like a way to query a table to find out how much space it is using, like it's percentage for example, is there a way to do that? Excuse me if these questions have been asked before, I tried to search the LIST area and I also did not see anything related to this in the FAQS. Thanks for any info you can provide me. -- Rich Parker http://www.fssi-ca.com mailto:RichParker@fssi-ca.com ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
you can retrieve any information of tables by queryng the catalog of your database. For example, to get a description of table "sales", you can query:
SELECT
C.relname, A.attname, T.typname
FROM
pg_class C
INNER JOIN pg_attribute A ON(C.oid=A.attrelid)
INNER JOIN pg_type T ON (T.oid=A.atttypid)
WHERE
C.relname='sales';
from perl, php, c or whatever.
Here (http://www.postgresql.org/docs/7.3/interactive/catalogs.html) is the catalog tables documentation.
About your second question, you need to install contrb/dbsize. Then you can query
SELECT relation_size('sales');
to get the size of your sales table.
On Fri, 2003-08-15 at 12:35, Rich Parker wrote:
SELECT
C.relname, A.attname, T.typname
FROM
pg_class C
INNER JOIN pg_attribute A ON(C.oid=A.attrelid)
INNER JOIN pg_type T ON (T.oid=A.atttypid)
WHERE
C.relname='sales';
from perl, php, c or whatever.
Here (http://www.postgresql.org/docs/7.3/interactive/catalogs.html) is the catalog tables documentation.
About your second question, you need to install contrb/dbsize. Then you can query
SELECT relation_size('sales');
to get the size of your sales table.
On Fri, 2003-08-15 at 12:35, Rich Parker wrote:
Hello everyone. I have been using PostgreSQL for about 3-4 years now, it's wonderful how I can make it do things and write Perl programs to interface with it. Since I do all of the DBA behind the scenes on our company Intranet (Linux-Apache based), and I really prefer writing Perl programs to do my simple admin functions. I really like those \d commands (via telnet) and they tell me a lot of what I need for writing new programs etc, but I would really like a way to use those through my Perl programs, but haven't found a way to do so, has anyone done this in this manner yet? Also, I'd like a way to query a table to find out how much space it is using, like it's percentage for example, is there a way to do that? Excuse me if these questions have been asked before, I tried to search the LIST area and I also did not see anything related to this in the FAQS. Thanks for any info you can provide me.
Attachment
Thanks for the info, But I can make the psql part work, but I get an invalid command type of msg from the \d attempt, I tried many ways to do this to no avail. So I tried both a SELECT to pg_class (And similar catalog procedures) and this attempt at a system call. Any other ideas?? Thanks everyone, so far. Williams, Travis L wrote: > @results = `print \'\\d\' |/usr/local/pgsql/bin/psql travis`; > > > Travis > > -----Original Message----- > From: Rich Parker [mailto:RichParker@fssi-ca.com] > Sent: Friday, August 15, 2003 10:35 AM > To: pgsql-general@postgresql.org > Subject: [GENERAL] New to list, quick question. > > > Hello everyone. > I have been using PostgreSQL for about 3-4 years now, it's wonderful how > > I can make it do things and write Perl programs to interface with it. > > Since I do all of the DBA behind the scenes on our company Intranet > (Linux-Apache based), and I really prefer writing Perl programs to do my > > simple admin functions. I really like those \d commands (via telnet) and > > they tell me a lot of what I need for writing new programs etc, but I > would really like a way to use those through my Perl programs, but > haven't found a way to do so, has anyone done this in this manner yet? > Also, I'd like a way to query a table to find out how much space it is > using, like it's percentage for example, is there a way to do that? > > Excuse me if these questions have been asked before, I tried to search > the LIST area and I also did not see anything related to this in the > FAQS. > > Thanks for any info you can provide me. -- Rich Parker http://www.fssi-ca.com mailto:RichParker@fssi-ca.com
Rich Parker <RichParker@fssi-ca.com> writes:
>> @results = `print \'\\d\' |/usr/local/pgsql/bin/psql travis`;
Think you want "echo" not "print".
regards, tom lane
Works either way. But echo is probably more common.
Travis
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Saturday, August 16, 2003 7:21 AM
To: Rich Parker
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] New to list, quick question.
Rich Parker <RichParker@fssi-ca.com> writes:
>> @results = `print \'\\d\' |/usr/local/pgsql/bin/psql travis`;
Think you want "echo" not "print".
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
Thanks guys, The ECHO was the cure, for some reason the PRINT just gave me an error when I used it. So I got TWO great responses from this LIST for my questions, THANKS!! One with a "Call" using Telnet type of commands, the other using SQL SELECT options to get everything I need. You guys (And gals as needed) sure are providing a great list for Questions and answers. Thanks... Williams, Travis L wrote: > Works either way. But echo is probably more common. > > Travis > > -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Sent: Saturday, August 16, 2003 7:21 AM > To: Rich Parker > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] New to list, quick question. > > > Rich Parker <RichParker@fssi-ca.com> writes: > >>>@results = `print \'\\d\' |/usr/local/pgsql/bin/psql travis`; > > > Think you want "echo" not "print". > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > > -- Rich Parker http://www.fssi-ca.com mailto:RichParker@fssi-ca.com