parsing relname in pg_class
Howdy:
Running Postgres 7.1.3 on RedHat 7.2 kernel 2.4.7 rel. 10.
I'm writing a perl script where I only want to parse
out a list of tables from the database I'm connecting
to.
I'm doing something like this:
[snip]
select relname from pg_class
where relname not like '%_i%';
[/snip]
And that will get me halfway, but I still get a list
of tables with numbers attached and primary keys and the
like (for instance, what is pg_toast?)
Is there an easier way to get a list of the tables ONLY
with a select statement? I don't think I can pass a "\d"
as a variable and get a return and I don't want to make
a case statement for every possibility.
PS: If this comes through as a MIME-type email, let me know -
I'm trying to stop that (using M$ Outlook '97).
Thanks!
-X
--thanks for emailing:
--not sure what the 'r' in reltype
is, but i'm getting an error. reltype
is defined as oid ... should there be
a letter next to it?
[snip error]
bcn=> select relname from pg_class
bcn-> where reltype = 'r'
bcn-> and relanem not like 'pg%';
ERROR: oidin: error in "r": can't parse "r"
[/snip error]
--not sure, but i think i can use this:
[sql]
select relname from pg_class where
relname not like '%_i'
and relname !~ '[$0-9]'
and relname not like 'pg_%'
order by 1;
[/sql]
--but i have to say, it looks like it's a
bit much (more work than needed) ...
oh well ... at least i'm learning ...
--thanks again!
-X
-----Original Message-----
From: Andrew Sullivan [mailto:andrew@libertyrms.info]
On Mon, Apr 01, 2002 at 04:29:34PM -0500, Johnson, Shaunn wrote:
Howdy:
Running Postgres 7.1.3 on RedHat 7.2 kernel 2.4.7 rel. 10.
I'm writing a perl script where I only want to parse
out a list of tables from the database I'm connecting
to.I'm doing something like this:
[snip]
select relname from pg_class
where relname not like '%_i%';[/snip]
What about
SELECT relname FROM pg_class
WHERE reltype = 'r'
AND relname not like 'pg%'
?
[snip]
Import Notes
Resolved by subject fallback
On Mon, Apr 01, 2002 at 04:51:52PM -0500, Johnson, Shaunn wrote:
--thanks for emailing:
--not sure what the 'r' in reltype
Sorry, that should have been 'relkind'.
A
----
Andrew Sullivan 87 Mowat Avenue
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M6K 3E3
+1 416 646 3304 x110
On Mon, 1 Apr 2002 16:29:34 -0500
"Johnson, Shaunn" <SJohnson6@bcbsm.com> wrote:
I'm writing a perl script where I only want to parse
out a list of tables from the database I'm connecting
to.
If it's Perl with DBD, $dbh->tables() will get it for you.
Is there an easier way to get a list of the tables ONLY
with a select statement?
select * from pg_tables;
should do the trick. If you're not interested in system catalogs, filter
out all the relations that start with "pg_".
I don't think I can pass a "\d"
as a variable and get a return and I don't want to make
a case statement for every possibility.
All these kinds of queries can be obtained by starting psql with "-E",
and then taking a look at how psql itself is getting the information.
Cheers,
Neil
--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC
"Johnson, Shaunn" <SJohnson6@bcbsm.com> writes:
I'm writing a perl script where I only want to parse
out a list of tables from the database I'm connecting
to.
select relname from pg_class
where relname not like '%_i%';
That doesn't seem very helpful; what's worse is it might hide tables you
want. Something like
select relname from pg_class
where relname not like 'pg\\_%' and relkind = 'r';
would probably be a lot closer to what you want.
regards, tom lane